(Should be) simple recordcount

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

(Should be) simple recordcount

Author

Topic

Vinco83
Starting Member

3 Posts

Posted - 2010-07-28 : 10:14:18

Hey everyone,

I've got a problem that I can't seem to wrap my head around and figure out. It should be pretty straight forward and any help would be greatly appreciated!

I've got 4 tables laid out like so:

Items
--ItemNumber
--Item Description

JobSetup
--ItemNumber (references Items.ItemNumber)
--CylinderNumber (references Cylinder.CylinderNumber)

Cylinder
--CylinderNumber
--ColorID (references Color.ColorID)

Color
--ColorID
--ColorName

My client simply wants the number of colors for each ITEM. As you can see there are multiple Cylinders for each Item and multiple colors for each Cylinder.

I've tried the following query:

select i.ItemNumber, i.ItemDescription, count(c.ColorID)
from Items i 
inner join JobSetup j on i.ItemNumber = j.ItemNumber
inner join Cylinder c on c.CylinderNumber = j.CylinderNumber
inner join Color co on co.ColorID = c.ColorID
group by i.ItemNumber, i.ItemDescription, c.ColorID

and it returns data (customer wants just the ItemNumber, ItemDescription and Number of colors returned)...however, the color count seems to be wrong.

Can anyone help??

Please? lol

Thanks in advance!!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-28 : 10:19:44

Please provide some sample data and expected output too.

Vinco83
Starting Member

3 Posts

Posted - 2010-07-28 : 10:38:03

quote:
Originally posted by vijayisonly

Please provide some sample data and expected output too.

"Items" table:

ItemNumber    Item Description
----------          ---------------
1234                Graphics

"JobSetup" table:
ItemNumber     CylinderNumber
----------     --------------
1234            1
1234            2
1234            3
1234            4
1234            5
1234            6
1234            7
1234            8
1234            9
1234            10
1234            11
1234            12

"Cylinder" table
CylinderNumber     ColorID
--------------     -------
1                  50
1                  5
2                  36
3                  3
4                  52
4                  5
5                  52
6                  12
7                  4
8                  6
9                  50
10                 50
11                 9
12                 10

"Color" table
ColorID     ColorName
-------     ---------
36           Red
3            Blue
5            Green
52           Yellow
12           Purple
4            Teal
6            Magenta
50           Gray
50           Gray
9            Black
10           Brown

Expected output is:


ItemNumber    ItemDescription    NumOfColors
----------    ---------------    -----------
1234           Graphics           14

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-28 : 10:44:29

Do a series of JOINs between the tables and you should get a draft of the final solution.
Add a GROUP BY and a COUNT (perhaps using DISTINCT) and you are done!

N 56°04'39.26"
E 12°55'05.63"

Vinco83
Starting Member

3 Posts

Posted - 2010-07-28 : 13:49:28

quote:
Originally posted by Peso

Do a series of JOINs between the tables and you should get a draft of the final solution.
Add a GROUP BY and a COUNT (perhaps using DISTINCT) and you are done!

N 56°04'39.26"
E 12°55'05.63"

I solved this by removing the inner join on the Cylinders and Color tables. I went back and checked and there are only one color per Cylinder, so really the count of the Cylinders for each item would have worked as well.

I used the following query:

select i.ItemNumber, i.ItemDescription, count(c.ColorID)
from Items i 
inner join JobSetup j on i.ItemNumber = j.ItemNumber
inner join Cylinder c on c.CylinderNumber = j.CylinderNumber
group by i.ItemNumber, i.ItemDescription
order by i.ItemNumber, i.ItemDescription

Thanks for the responses and the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources