question

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

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

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

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

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

Hi Thomas, I fix the statistics query by using subquery and union. Thanks for your attemp to help me.