nested group by select in SQL Server

Question

I am doing the following SELECT statement to see how many Reps each of my Manufacturers have in my database, this is working nicely:

SELECT ManufacturerId, COUNT(*) AS RepCount 
FROM ManufacturerSalesReps WHERE IsDeleted=0
GROUP BY ManufacturerID
ORDER BY  COUNT(*)

So this gives me the ManufacturerId and the number of reps but what i really need is the number of manufacturers at different rep counts. So I want to GROUP the results from the above SELECT by RepCount.

How do I accomplish this?

Evaldas Buinauskas · Accepted Answer · 2015-02-25 18:14:57Z

2

I cannot think of something else, but:

SELECT T.RepCount, COUNT(*) AS ManufacturerCount
FROM (
    SELECT ManufacturerId, COUNT(*) AS RepCount 
    FROM ManufacturerSalesReps
    WHERE IsDeleted=0
    GROUP BY ManufacturerID
) AS T
GROUP BY T.RepCount
ORDER BY  COUNT(*)

Either this is correct or totally dumb.

answered Feb 25, 2015 at 18:14

Evaldas Buinauskas

14.2k11 gold badges63 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

iruvar · Accepted Answer · 2015-02-25 18:25:13Z

0

With windowing functions

SELECT ManufacturerId, COUNT(*), 
count(ManufacturerId) over (partition by COUNT(*)) num_man AS RepCount 
FROM ManufacturerSalesReps WHERE IsDeleted=0
GROUP BY ManufacturerID
ORDER BY  2

answered Feb 25, 2015 at 18:25

iruvar

23.5k7 gold badges58 silver badges83 bronze badges

Collectives™ on Stack Overflow

nested group by select in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related