Aggregates - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Aggregates

Author

Topic

ramdas
Posting Yak Master

181 Posts

Posted - 2003-04-15 : 09:58:59

Hi Folks,
I have a query like this:

SELECT COUNT(*) AS catCount, SC.abbr_summary_code, SL.location_name
FROM tblSiebelExtract SE
INNER JOIN tblSiebelLocation SL ON SE.location_id = SL.location_id
INNER JOIN tblSiebelCategory SC ON SE.acct_closing_rsn = SC.closed_code
WHERE SE.created_date between '1/1/2003' and '4/1/2003' and (SL.location_code = 'ABLVS')
GROUP BY SC.abbr_summary_code, SL.location_name
ORDER BY COUNT(*) DESC, SC.abbr_summary_code

What I need to be able to do is also calculate the sum of catcount which is intially derived from count(*). Is there a way to do it in a single query?

Ramdas Narayanan
SQL Server DBA

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-15 : 10:07:50

Try:

SELECT COUNT(*) AS catCount, SC.abbr_summary_code, SL.location_name
FROM tblSiebelExtract SE
INNER JOIN tblSiebelLocation SL ON SE.location_id = SL.location_id
INNER JOIN tblSiebelCategory SC ON SE.acct_closing_rsn = SC.closed_code
WHERE SE.created_date between '1/1/2003' and '4/1/2003' and (SL.location_code = 'ABLVS')
GROUP BY SC.abbr_summary_code, SL.location_name
ORDER BY COUNT(*) DESC, SC.abbr_summary_code
COMPUTE SUM(COUNT(*))

OS

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-15 : 10:17:21

SELECT COUNT(*) AS catCount,
case grouping(sc.abbr_summary_code) when 1 then 'TOTAL' else SC.abbr_summary_code end as abbr_summary_code,
case grouping(SL.location_name) when 1 then 'TOTAL' else SL.location_name end as location_name
FROM tblSiebelExtract SE
INNER JOIN tblSiebelLocation SL ON SE.location_id = SL.location_id
INNER JOIN tblSiebelCategory SC ON SE.acct_closing_rsn = SC.closed_code
WHERE SE.created_date between '1/1/2003' and '4/1/2003' and (SL.location_code = 'ABLVS')
GROUP BY SC.abbr_summary_code, SL.location_name with rollup
having grouping(sc.abbr_summary_code) + grouping(SL.location_name) <> 1
ORDER BY COUNT(*) DESC, SC.abbr_summary_code

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 10:18:10

Ummm..what?

quote:

What I need to be able to do is also calculate the sum of catcount which is intially derived from count(*).

Isn't what you're asking for just:

SELECT COUNT(*) AS catCount
FROM tblSiebelExtract SE
INNER JOIN tblSiebelLocation SL ON SE.location_id = SL.location_id
INNER JOIN tblSiebelCategory SC ON SE.acct_closing_rsn = SC.closed_code
WHERE SE.created_date between '1/1/2003' and '4/1/2003' and (SL.location_code = 'ABLVS')

because that what it seems like...

Also in a COMPUTE SQL extension, don't you need:

BOL:

BY expression

Generates control-breaks and subtotals in the result set. expression is an exact copy of an order_by_expression in the associated ORDER BY clause. Typically, this is a column name or column alias. Multiple expressions can be specified. Listing multiple expressions after BY breaks a group into subgroups and applies the aggregate function at each level of grouping.

If you use COMPUTE BY, you must also use an ORDER BY clause. The expressions must be identical to or a subset of those listed after ORDER BY, and must be in the same sequence. For example, if the ORDER BY clause is:

ORDER BY a, b, c

The COMPUTE clause can be any (or all) of these:

COMPUTE BY a, b, c
COMPUTE BY a, b
COMPUTE BY a

?????????????????????????????????????

Never used it so I'm not sure.

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-15 : 10:32:13

Well, yeah Brett, the BOL does mention that you need an expression to go with the COMPUTE, but it seems to be working even without it. Hey, i'm not complaining! Guess its one of those undocumented thingys...

OS

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-15 : 10:37:43

quote:

Ummm..what?

quote:

What I need to be able to do is also calculate the sum of catcount which is intially derived from count(*).

Isn't what you're asking for just:

I assumed ramdas wanted to include a TOTAL in the generated rowset ...

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 10:42:59

Don't know....seemed to be on the vague side to me (unlike the rest of the posts....

)

but then, I'm just a humble window washer.....

Brett

8-)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-15 : 10:44:17

Well, even if it wasn't what ramdas wanted, you have to admint, my having clause and the with rollup is pretty fly, eh?

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 10:51:24

Never seen a grouping clause like that before (dude!)

But does it work?

Hey ramdas...gotta try Jay's solution after all. Let us know.

and to think...last week was your anniversary....happy one year.

Brett

8-)

Edited by - x002548 on 04/15/2003 10:52:53

ramdas
Posting Yak Master

181 Posts

Posted - 2003-04-16 : 16:54:24

Hi folks,
Thanks for your help. Both the solutions worked. Thanks to Jay, it is more cleaner since the compute by puts a header sum with the toal at the bottom.

Bye
Ramdas

Ramdas Narayanan
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources