# question

## 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
img1.png (17.3 KiB)
img2.png (11.1 KiB)

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

·
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

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 ·
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

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 ·
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

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

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

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 ·
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')

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