Trying to work out numerous counts

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)

Trying to work out numerous counts

Author

Topic

pmwhelan
Starting Member

5 Posts

Posted - 2010-08-25 : 06:11:49

Hi

I have two tables 'Stations' and 'AnnualData'.

Stations is a list of numerous stations around the world that hold temperature data.

AnnualData is the data that these stations have recorded.

I have Stations.NightLight which is either 'A', 'B' or 'C'
and Stations.StationType which is either 'R', 'U' or 'S'

Would it be possible for every year (AnnualData.Year) to find out the amount of stations that are 'A' and 'R'?
Then the ones that are 'A' and 'U' etc

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-25 : 06:51:50

sample data and output should really make this one easy for people to help you with answers.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-25 : 06:53:59

Seems you need to use PIVOT. Search for the same in this forum

Madhivanan

Failing to plan is Planning to fail

pmwhelan
Starting Member

5 Posts

Posted - 2010-08-25 : 07:45:15

Stations
--------
StationCode | NightLight | StationType
1 | A | U
2 | B | S
3 | A | S
4 | C | U
5 | C | S
6 | A | R
7 | A | S
8 | B | U
9 | A | S
10 | C | R
11 | C | U
12 | A | S

AnnualData
----------
StationCode | Year
1 | 2010
2 | 2010
3 | 2010
4 | 2010
5 | 2010
6 | 2010
7 | 2010
8 | 2010
9 | 2010
10 | 2010
11 | 2010
12 | 2009 (so these is no row here for 2010)

So in this case I'd like to return 9 columns
AR | AU | AS | BR | BU | BS | CR | CU | CS
1 | 1 | 3 | 0 | 1 | 1 | 1 | 2 | 1

thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-25 : 08:13:50

If those are the only 9 combinations then you can use case when
SELECT
SUM(CASE WHEN NightLight = 'A' and StationType = 'R' THEN 1 ELSE 0 END) as AR
, SUM(CASE WHEN NightLight = 'A' and StationType = 'S' THEN 1 ELSE 0 END) as [AS]
, SUM(CASE WHEN NightLight = 'A' and StationType = 'U' THEN 1 ELSE 0 END) as AU

etc

Jim

Everyday I learn something that somebody else already knew

pmwhelan
Starting Member

5 Posts

Posted - 2010-08-25 : 08:45:06

Thanks jimf that's brilliant!

It's almost perfect.

It returns one row.
I would like it to return a row for every 'year' in my annual data.

So it should return ....

So in this case I'd like to return 9 columns
Year | AR | AU | AS | BR | BU | BS | CR | CU | CS
2010 | 1 | 1 | 3 | 0 | 1 | 1 | 1 | 2 | 1
2009 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0

thanks so much

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-08-25 : 08:49:32

This should really be done in the front end. As you can see, SQL can be quite messy for changing the presentation of data.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-25 : 09:00:38

You can join to that table



SELECT
 a.[Year]
,SUM(CASE WHEN NightLight = 'A' and StationType = 'R' THEN 1 ELSE 0 END) as AR
, SUM(CASE WHEN NightLight = 'A' and StationType = 'S' THEN 1 ELSE 0 END) as [AS]
, SUM(CASE WHEN NightLight = 'A' and StationType = 'U' THEN 1 ELSE 0 END) as AU
FROM  Stations s
INNER JOIN   AnnualData a ON s.StationCode = a.StationCode

GROUP BY a.[year]

Jim

Everyday I learn something that somebody else already knew

pmwhelan
Starting Member

5 Posts

Posted - 2010-08-25 : 10:55:03

thanks jimf you're a lifesaver
that worked a charm!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-25 : 10:57:29

Just remember, if you get a new combination of {NightLight, StationType}
you will have to add a new line to the "SUM(CASE..." part of the query.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources