merge several group by counts into new one table

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)

merge several group by counts into new one table

Author

Topic

spinningtop
Starting Member

29 Posts

Posted - 2012-03-07 : 09:24:40

Hi

I have a table (table1) as below with a storeid, staffid salaryband and staffsex columns. From this I would like to create a new table grouped by storeID with a new field
with a count of salaryband and staffsex by storeid. So the results would look like the table2 below.
I can use a group by query as below to get each individual new column as below but I need to combine all these counts into one new table as table2 below. I can mangage it by making several
tables and joining these on storeid but I think there is a better way.

Any ideas?

Thanks


SELECT   storeid, Count(salaryband) AS 20000
 FROM table1
WHERE salaryband = '20000'
GROUP BY  storeid

table1

storeid staffid salaryband staffsex
1 A 20000 male
1 B 20000 female
2 A 30000 female
3 A 20000 female
4 A 20000 male
4 B 30000 male
1 C 30000 female
4 C 40000 male

table2

storeid 20000 30000 40000 malestaff femalestaff
1 2 1 Null 1 2
2 Null 1 Null Null 1
3 1 Null Null Null 1
4 1 1 1 3 Null

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-03-07 : 10:24:27

Instead of a new table, why not a view?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

spinningtop
Starting Member

29 Posts

Posted - 2012-03-07 : 14:56:57

Thanks this id probably better. I created several views and joined them

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-07 : 15:12:01

and here I thought StaffSex should be a number

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources