Producing aggregate results without PIVOT

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

Producing aggregate results without PIVOT

Author

Topic

baps
Starting Member

3 Posts

Posted - 2009-10-11 : 12:25:40

Hi,

I've been learning about the PIVOT function using SQL Server 2005 and I've been testing it out in SSMS. I want to produce two columns with results of the individual States in a table and how many times each state appears in the table. The query below produces two seperate results so my question is how to join them into one result by using JOIN or something similar. The query is:

SELECT DISTINCT State FROM dbo.Table1
GROUP BY State;
SELECT COUNT(State) FROM dbo.Table1
GROUP BY State;

I'm trying to not name specific States in the query so that if new states are added, I won't need to adapt the query each time.

Thanks
B

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-11 : 13:11:30

SELECT State, COUNT(State) FROM dbo.Table1 GROUP BY State ORDER BY State

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-11 : 14:49:37

quote:
Originally posted by baps

Hi,

I've been learning about the PIVOT function using SQL Server 2005 and I've been testing it out in SSMS. I want to produce two columns with results of the individual States in a table and how many times each state appears in the table. The query below produces two seperate results so my question is how to join them into one result by using JOIN or something similar. The query is:

SELECT DISTINCT State FROM dbo.Table1
GROUP BY State;
SELECT COUNT(State) FROM dbo.Table1
GROUP BY State;

I'm trying to not name specific States in the query so that if new states are added, I won't need to adapt the query each time.

Thanks
B

whats the purpose of DISTINCT in first query? wont grouping by state give you only single occurance per state value?

baps
Starting Member

3 Posts

Posted - 2009-10-12 : 14:51:54

quote:
Originally posted by visakh16

quote:
Originally posted by baps

Hi,

I've been learning about the PIVOT function using SQL Server 2005 and I've been testing it out in SSMS. I want to produce two columns with results of the individual States in a table and how many times each state appears in the table. The query below produces two seperate results so my question is how to join them into one result by using JOIN or something similar. The query is:

SELECT DISTINCT State FROM dbo.Table1
GROUP BY State;
SELECT COUNT(State) FROM dbo.Table1
GROUP BY State;

I'm trying to not name specific States in the query so that if new states are added, I won't need to adapt the query each time.

Thanks
B

whats the purpose of DISTINCT in first query? wont grouping by state give you only single occurance per state value?

I was trying to single out State names without repeating them. Peso's suggestion above gives me exactly what I was aiming for. Thanks for the reply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources