PIVOT with Count - 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

PIVOT with Count

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-12-06 : 14:58:25

I have this data of Tracking no and the destination state in my_table:

Tracking #   Destination
123          CA
446          WA
789          MA
521          CA
325          MA
995          CA
958          WA
650          MA
235          WY
238          CA

How Do I know
a) how many packets are present for each destination state?

CA - 4
MA - 3
WA - 2
WY - 1

b) Top 2 States with Highest no of pakages

CA - 4
MA - 3

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-06 : 15:12:14

SELECT Destination, Count(*) FROM TrackingTable GROUP BY Destination ORDER BY Count(*) DESC

SELECT TOP 2 Destination, Count(*) FROM TrackingTable GROUP BY Destination ORDER BY Count(*) DESC

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-12-06 : 16:22:19

Thanks robovolk. It works great.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-07 : 02:43:06

Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-12-07 : 03:19:14

quote:
Originally posted by madhivanan

Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan. It cleared my other doubts as well. :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-20 : 08:37:25

quote:
Originally posted by s_anr

quote:
Originally posted by madhivanan

Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan. It cleared my other doubts as well. :)

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources