question

isa.jasim avatar image
isa.jasim asked

select query to find statistics

I have three tables shown in the follow images, and I want the results by execute one query and join these tables like the results. Plz help me as soon as possible. ![alt text][1] ![alt text][2] [1]: /storage/temp/563-img1.png [2]: /storage/temp/564-img2.png
sql-server-2008homework
img1.png (17.3 KiB)
img2.png (11.1 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
As this is tagged as a homework question, I'll just point you in the direction of the [PIVOT][1] statement. [1]: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Fatherjack spoilsport. :-P
1 Like 1 ·
hmmm, could go a little deeper to explain theory maybe?!
0 Likes 0 ·
isa.jasim avatar image
isa.jasim answered
Hi I want to make statistics query to find count of cards, plates and stickers depending on, first Division , then Safe and in the last Storage like the follow: Division Safe Storage Cards Plates Stickers Total ------------------------------------------------------- (10 00 00 6 5 9 20) --Div1 (10 10 00 4 4 7 15) --Div1-Saf1 (10 10 10 2 1 2 5) --Div1-Saf1-Stor1 and so
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you provide your test data as text so we can replicate your scenario and offer some solutions?
1 Like 1 ·
Fatherjack avatar image
Fatherjack answered
Possibly easier to understand than PIVOT is the use of the CASE statement to test for each column. PIVOT works better for large and unknown column values, CASE will work well for fixed options (in my example there are only 4 cities, in your data there appear to be Cards, Plates and Stickers... DECLARE @People TABLE ( ID INT IDENTITY , TownID INT , [Name] VARCHAR(30) ) DECLARE @Town TABLE ( ID INT IDENTITY , TownName VARCHAR(30) ) INSERT @People ( [TownID], [Name] ) VALUES ( 1, -- TownID - int 'Barry' -- Name - varchar(30) ), ( 2, -- TownID - int 'Terry' -- Name - varchar(30) ), ( 3, -- TownID - int 'Harry' -- Name - varchar(30) ), ( 3, -- TownID - int 'Harry' -- Name - varchar(30) ), ( 2, -- TownID - int 'Gary' -- Name - varchar(30) ), ( 2, -- TownID - int 'Mo' -- Name - varchar(30) ), ( 1, -- TownID - int 'Curly' -- Name - varchar(30) ), ( 1, -- TownID - int 'Curly' -- Name - varchar(30) ) INSERT @Town ( [TownName] ) VALUES ( 'London' ), ( 'Paris' ), ( 'Hull' ), ( 'Amsterdam' ) SELECT [p].[Name] , [t].[TownName] FROM @People AS p INNER JOIN @Town AS t ON [p].[townID] = [t].[ID] SELECT [p].[Name] , SUM(CASE WHEN [t].[TownName] = 'London' THEN 1 ELSE 0 END) AS [London] , SUM(CASE WHEN [t].[TownName] = 'Paris' THEN 1 ELSE 0 END) AS [Paris] , SUM(CASE WHEN [t].[TownName] = 'Amsterdam' THEN 1 ELSE 0 END) AS [Amsterdam] , SUM(CASE WHEN [t].[TownName] = 'Hull' THEN 1 ELSE 0 END) AS [Hull] FROM @People AS p INNER JOIN @Town AS t ON [p].[townID] = [t].[ID] GROUP BY name
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Can't paste this as a comment, as it's too long. Source code for the data shown above: Use TempDB GO BEGIN TRAN CREATE TABLE Article (No int, Storage_code char(8), Type_code char(1)) INSERT INTO Article VALUES (1, '10 00 00', 'S'), (2, '10 10 00', 'P'), (1, '10 10 10', 'S'), (3, '11 00 00', 'C'), (1, '10 00 00', 'C'), (2, '11 10 10', 'P') SELECT * FROM Article Create TABLE Types (Code char(1), Name varchar(20)) INSERT INTO Types VALUES ('C', 'Cards'), ('P', 'Plates'), ('S', 'Stickers') SELECT * FROM Types CREATE TABLE Storage (Main_Code char(8), Division_Code int, Safe_Code int, Position_Code int, Main_Name varchar(50)) INSERT INTO Storage VALUES ('10 00 00', 10, 00, 00, 'Div1'), ('10 10 00', 10, 10, 00, 'Div1_Saf1'), ('10 10 10', 10, 10, 10, 'Div1_Saf1_Pos1'), ('10 10 11', 10, 10, 11, 'Div1_Saf1_Pos2'), ('10 10 12', 10, 10, 12, 'Div1_Saf1_Pos3'), ('10 11 00', 10, 11, 00, 'Div1_Saf2'), ('10 11 10', 10, 11, 10, 'Div1_Saf2_Pos1'), ('10 11 11', 10, 11, 11, 'Div1_Saf2_Pos2'), ('10 11 12', 10, 11, 12, 'Div1_Saf2_Pos3'), ('11 00 00', 11, 00, 00, 'Div2'), ('11 10 00', 11, 10, 00, 'Div2_Saf1'), ('11 10 10', 11, 10, 10, 'Div2_Saf1_Pos1'), ('11 10 11', 11, 10, 11, 'Div2_Saf1_Pos2'), ('11 10 12', 11, 10, 12, 'Div2_Saf1_Pos3'), ('11 11 00', 11, 11, 00, 'Div2_Saf2'), ('11 11 10', 11, 11, 10, 'Div2_Saf2_Pos1'), ('11 11 11', 11, 11, 11, 'Div2_Saf2_Pos2'), ('11 11 12', 11, 11, 12, 'Div2_Saf2_Pos3') SELECT * FROM Storage ROLLBACK
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Someone got OCR there?
0 Likes 0 ·
Nah, I was beginning to work on the query to figure out how I would do it, and then realised that the figures weren't making any sense. Now I've seen the full scale of what's required, it's not surprising that I wasn't getting it!
0 Likes 0 ·
isa.jasim avatar image
isa.jasim answered
ceate table article (no varchar(1) primary key, Storage_Code varchar(6), Type_Code varchar(1) , foreign key (Storage_Code) references stoarge(main_code) , foreign key (Type_Code) references types(code)) T01_PERSON(C01_PERSON_NO) insert into article values ('1','100000','S') insert into article values ('2','101000','P') insert into article values ('1','101010','S') insert into article values ('3','110000','C') insert into article values ('1','100000','C') insert into article values ('2','111010','P') create table storage (main_code varchar(6) primary key, division_code varchar(2), safe_code varchar(2), position_code varchar(2) , Name varchar(40)) insert into storage values ('100000','10','00','00','Div1') insert into storage values ('101000','10','10','00','Div1_Saf1) insert into storage values ('101010','10','10','10','Div1_Saf1_Pos1') insert into storage values ('101011','10','10','11','Div1_Saf1_Pos2') insert into storage values ('101100','10','11','00','Div1_Saf2') insert into storage values ('101110','10','11','10','Div1_Saf2_Pos1') insert into storage values ('101111','10','11','11','Div1_Saf2_Pos2') insert into storage values ('101112','10','11','12','Div1_Saf2_Pos3') insert into storage values ('110000','11','00','00','Div2') insert into storage values ('111000','11','10','00','Div2_Saf1) insert into storage values ('111010','11','10','10','Div2_Saf1_Pos1') insert into storage values ('111011','11','10','11','Div2_Saf1_Pos2') -- ........ and so create table types(code varchar(1) primary key, name varchar(10)) insert into types values('C','Cards') insert into types values('P','Plates') insert into types values('S','Sticker')
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

isa.jasim avatar image
isa.jasim answered
Hi Thomas, I fix the statistics query by using subquery and union. Thanks for your attemp to help me.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.