A single query to sum an aggregate - can this be done?

Question

Post reply

A single query to sum an aggregate - can this be done?

dave hants

Default port

Points: 1485
More actions
March 9, 2011 at 8:53 am

#236259

Hi,
Using SQL Server 2000 I'm trying to construct a single query to sum an aggregate. Can this be done?
For example, I have a table containing a list of part numbers. There are some duplicates/triples in the table.
I can see the dupes and how many duplicates there are of each part number using this:
select partno, count(*) as mycount
from partno_table
group by partno
order by mycount desc
What I would now like to do is get a total of mycount.
Using this throws an error because SUM does not allow subqueries or aggregates:
select sum(
select count(*) as mycount
from partno_table
group by partno
order by mycount desc
)
Is there a way to get the SUM of mycount?
Any ideas appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Maybe this?

select sum(mycount)

from (

select count(*) as mycount

from partno_table

group by partno

)x

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Have you looked at ROLLUP or CUBE as options with the aggregate?

dave hants Default port Points: 1485 More actions · Answer 3

Thanks Mark-101232. Brilliant. 🙂

But I had already tried this, without the final x, which gives me a syntax error.

What does the final x do? (Apart from making it work 🙂 )

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

The SELECT inside the brackets is known as a derived table, the 'x' is simply to give it a name (which is required by SQL Server)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

dave hants Default port Points: 1485 More actions · Answer 5

Great, thanks Mark. That makes sense. Pity the error message didn't tell me to add a name for the derived table, instead of just saying "Syntax error near ')'" :angry:

Many thanks too to Steve Jones. I just tried the rollup and cube options - they work too, many thanks, hadn't looked at those before.

Thanks guys, best regards,

Dave 🙂

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 6

select count(*) as mycount

from partno_table

...is the same this as summing the individual counts.

Eddie Wuerch
MCM: SQL

dave hants Default port Points: 1485 More actions · Answer 7

Hi Eddie,

Yes, that's what I needed to do - sum the individual duplicate counts... to reconcile against the total number of rows in the table.