Aggregate woes - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Aggregate woes

Author

Topic

gravyface
Starting Member

16 Posts

Posted - 2009-01-21 : 15:47:50

Can't wrap my head around how to do this the "proper" way:

Have a table called "cars"; here's what the structure/data looks like:

make_1 make_2 make_3 color_1 color_2 color_3
-----------------------------------------------
Ford Chevy Dodge Blue Green Red
Toyota Toyota Chevy Yellow Yellow Green
BMW Ford NULL Red Red NULL

There's a set list of colors and makes that can be populated in any of the corresponding column types. The fields are related like the following for each row:

make_1 = color_1
make_2 = color_2
make_3 = color_3

colors:
-------
Red
Green
Yellow
Blue

Makes:
------
Ford
Toyota
Chevy
Dodge
BMW

What I need to calculate is something like this:

Total # of Toyotas that are Red: 0
Total # of Toyotas that are Green: 0
Total # of Toyotas that are Yellow: 2
Total # of Toyotas that are Blue: 0

Rinse and repeat for each make.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-21 : 16:09:21

[code]select
Car_brand,Car_Color,Countof=count(*)
from
(select make_1 as Car_brand,color_1 as Car_Color
from
cars
union all
select make_2,color_2
from
cars
union all
select make_3,color_3
from
cars)t
group by
Car_brand,Car_Color[/code]

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-21 : 16:18:22

Almost looks like a homework problem

gravyface
Starting Member

16 Posts

Posted - 2009-01-21 : 16:19:42

quote:

Originally posted by sakets_2000

select 
	Car_brand,Car_Color,Countof=count(*)
from
	(select make_1 as Car_brand,color_1 as Car_Color
	from
	cars
	union all
	select make_2,color_2
	from
	cars
	union all
	select make_3,color_3
	from
	cars)t
group by
	Car_brand,Car_Color

If you don't mind, can you walk me through what's actually going on here? If you don't have time, and if I wanted to do further reading/learning, what method/theory/thought process led you to this conclusion?

Thank you.

gravyface
Starting Member

16 Posts

Posted - 2009-01-21 : 16:38:20

quote:
Originally posted by Skorch

Almost looks like a homework problem

Sadly, it's not -- just a computer guy asked to do up a report because I "know" SQL.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-21 : 16:46:55

I don't understand the reason for having 3 columns of car makes and 3 colors rather than 1 column for cars and 1 for colors.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-21 : 16:47:28

Then tell him to normalize his tables.
It makes thing a lot easier.

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

gravyface
Starting Member

16 Posts

Posted - 2009-01-21 : 16:48:57

I don't either: it's part of a COTS application and that's how they handle custom fields, so I don't really have a choice on design. That was the only way to have key/value pairs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources