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.
| Author |
Topic |
|
hobbes
Starting Member
3 Posts |
 Posted - 2005-03-09 : 12:32:37
|
| Hi, i've got a question which i'm not sure can be solved by a straightforward select and group by query, but thought if its possible you guys would know how!i've got a list of shops, and in each shop a number of people of different ages.the database tables are: [shop] and [person][person] has an age field and a foreign key to the shop table to show which shop they work in.i need to write a query which shows how many people of each age work in each shopthe query would display the number of people with that age in the columns and shops in the row like so: 18 19 20 21 etcshop1 1 4 2 7shop2 3 2 5 6shop3 4 6 1 1the only way i can think of doing it is to create another table and use a group by shop, age query in a cursor to update the correct shop and age field, which seems a bit of a long way around to do it.any advice would be gratefully received!Simon. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-09 : 12:43:03
|
| Is this for a class?SELECT Shop, SUM(CASE WHEN Age = 18 Then 1 ELSE 0) AS Age_18, SUM(CASE WHEN Age = 19 Then 1 ELSE 0) AS Age_19, SUM(CASE WHEN Age = 20 Then 1 ELSE 0) AS Age_20FROM.....Also sounds illegalBrett8-) |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-09 : 13:35:37
|
| here's some sample data:name, Age, Shop, HoursPerDay, HourlyRate"Billy",12,"shop 1",19, .50"Sally",8, "shop 2",17, .75"Junior",11,"shop 3",21, 1.12- Jeff |
|
|
|
hobbes
Starting Member
3 Posts |
Posted - 2005-03-10 : 03:51:06
|
| brett - you are a star! that works perfectly, and with rollup means i get a totals row too! :)btw it wasnt dodgy, honest!Simon. |
|
|
|
|
|
|
|
|
how many people of each age working in each shop