count and split - 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

General SQL Server Forums

New to SQL Server Programming

count and split

Author

Topic

kiwi1066
Starting Member

2 Posts

Posted - 2010-10-07 : 09:02:38

I am very new to this, what I am trying to do is end up with output that looks like this...


country
                     one   two   three   four   five
Australia             5     7      2       12    23
Christmas Island      3     12     5       9     16
Poland                12    6      14      24    12
USA                   5     3      23      16    21

The database tables look like this


table: activity

nick          date            activity
bob123      07/04/2010           12
bob123      07/05/2010           23
bob123      07/06/2010           47
bob123      07/07/2010           32
bob123      07/08/2010           54
bob123      07/09/2010           16
bob123      07/10/2010           53
joe772      07/04/2010           35
joe772      07/05/2010           47
joe772      07/06/2010           17
joe772      07/07/2010           56
joe772      07/08/2010           34


table:  people

people_id       people_nick
111                 bob123
112                 joe772


table:  address

people_id       country_id
111                13
112                24


table:  country

country_id      country_name
13                 australia
24                 christmas island

the tables above are a mock up.

This is what I am tring to do...
1. On the Activity table count the number of acitivy between 10 -19 (goes in the 'one' column), 20 - 29 (goes in the 'two' column), 30 - 39(goes in the 'three' column), 40 - 49(goes in the 'four' column), 50 - 59(goes in the 'five' column) for each country

I though that sounded easy but trying to do it I can get to doing multiple COUNT statements to do the first bit, but can't work our how seperate it into country.

Would someone mind showing me how to write this query and explain it as they went,

This would be much appreciated.

jleitao
Posting Yak Master

100 Posts

Posted - 2010-10-07 : 09:48:44

hi kiwi1066,

I don't test it but i think it works.

SELECT
AUX.COUNTRY,
SUM(ONE) AS ONE,
SUM(TWO) AS TWO,
SUM(THREE) AS THREE,
SUM(FOUR) AS FOUR,
SUM(FIVE) AS FIVE
FROM (

SELECT
CT.COUNTRY,
CASE
WHEN ACT.ACTIVITY >= 10 AND ACT.ACTIVITY <= 19 THEN 1
ELSE 0
END AS ONE,
CASE
WHEN ACT.ACTIVITY >= 20 AND ACT.ACTIVITY <= 29 THEN 1
ELSE 0
END AS TWO,
CASE
WHEN ACT.ACTIVITY >= 30 AND ACT.ACTIVITY <= 39 THEN 1
ELSE 0
END AS THREE,
CASE
WHEN ACT.ACTIVITY >= 40 AND ACT.ACTIVITY <= 49 THEN 1
ELSE 0
END AS FOUR,
CASE
WHEN ACT.ACTIVITY >= 50 AND ACT.ACTIVITY <= 59 THEN 1
ELSE 0
END AS FIVE
FROM ACTIVITY ACT
LEFT JOIN PEOPLE PL
ON ACT.NICK = PL.PEOPLE_NICK
LEFT JOIN ADDRESS AD
ON PL.PEOPLE_ID = AD.PEOPLE_ID
LEFT JOIN COUNTRY CT
ON AD.COUNTRY_ID = CT.COUNTRY_ID
) AUX
GROUP BY AUX.COUNTRY

kiwi1066
Starting Member

2 Posts

Posted - 2010-10-08 : 05:07:50

Hi jleitao

Thanks for your help.
It worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources