Need help with count and sum - 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

Transact-SQL (2000)

Need help with count and sum

Author

Topic

kpowell
Starting Member

4 Posts

Posted - 2004-10-12 : 12:31:19

I need a count of invoices per state and a total gross per state (by division and specific dates excluding some customers)

This is my query:

SELECT tbl1.division number,
tbl1.invoice number,
tbl1.gross amt,
tbl2.customer name,
tbl2.state
FROM tbl1, tbl2
WHERE tbl1.division = '405'
AND tbl1.acctdate <= 1040926
AND tbl1.acctdate >= 1040830
AND tbl1.status = 'I'
AND tbl1.billto = tbl2.custnumber
AND tbl1.commodity = tbl2.commodity
AND tbl2.custindicator = 'O'
AND tbl2.custnumber != '7210'
AND tbl2.custnumber != '6116'
AND tbl2.custnumber != '0602'
ORDER BY tbl2.state

my partial result set(each line represents one invoice):

Total gross State
amount Customer Name Code
------------- ------------------------------ -----
15751.44 GRAPHIC COMMUNICATIONS PP CA
15766.20 GRAPHIC COMMUNICATIONS PP CA
15774.12 GRAPHIC COMMUNICATIONS PP CA
15755.40 GRAPHIC COMMUNICATIONS PP CA
15769.44 GRAPHIC COMMUNICATIONS PP CA
11221.92 GRAPHIC COMMUNICATIONS PP CA
15757.56 GRAPHIC COMMUNICATIONS PP CA
15780.24 GRAPHIC COMMUNICATIONS PP CA
15762.96 GRAPHIC COMMUNICATIONS PP CA
15770.88 GRAPHIC COMMUNICATIONS PP CA
15955.56 GRAPHIC COMMUNICATIONS PP CA
15762.60 GRAPHIC COMMUNICATIONS PP CA
15760.80 GRAPHIC COMMUNICATIONS PP CA
15747.48 GRAPHIC COMMUNICATIONS PP CA
16259.66 GRAPHIC COMMUNICATIONS CA
16300.54 GRAPHIC COMMUNICATIONS CA
16139.21 GRAPHIC COMMUNICATIONS CA
16173.88 GRAPHIC COMMUNICATIONS CA
13359.73 GRAPHIC COMMUNICATIONS CA
16585.10 GRAPHIC COMMUNICATIONS CA
16622.72 GRAPHIC COMMUNICATIONS CA
16234.11 GRAPHIC COMMUNICATIONS CA
16640.20 GRAPHIC COMMUNICATIONS CA
16127.89 GRAPHIC COMMUNICATIONS CA
16270.24 GRAPHIC COMMUNICATIONS CA
--i need a total gross amount, count of invoice numbers would be nice
15942.96 R I S PAPER COMPANY CT
15942.24 R I S PAPER COMPANY CT
--i need a total gross amount, cound ot inboice numbers would be nice
52644.15 NORCOM INC GA
53129.31 NORCOM INC GA

I know there is a simple solution to this--I have not been using query that long. I am actually running this on a DB2--but the query should work whatever database type I use.

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-12 : 12:38:40


SELECT 
 tbl2.state
,tbl1.division number
,COUNT(*)
,SUM(tbl1.gross amt)
FROM tbl1, tbl2
WHERE tbl1.division = '405'
AND tbl1.acctdate <= 1040926
AND tbl1.acctdate >= 1040830
AND tbl1.status = 'I'
AND tbl1.billto = tbl2.custnumber 
AND tbl1.commodity = tbl2.commodity 
AND tbl2.custindicator = 'O'
AND tbl2.custnumber != '7210'
AND tbl2.custnumber != '6116'
AND tbl2.custnumber != '0602'
GROUP BY tbl2.state
,tbl1.division number

Brett

8-)

kpowell
Starting Member

4 Posts

Posted - 2004-10-12 : 13:03:23

That worked!!!! what a simple solution.

Thank You SO MUCH!!!

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-12 : 13:45:53

I get paid in margaritas....

Brett

8-)

kpowell
Starting Member

4 Posts

Posted - 2004-10-12 : 14:07:03

Believe me I would buy you a couple!

I dont suppose you can tell how to total the totals . . .

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-13 : 12:31:40

use rollup

Example

Declare  @risInv table (
         division int,
         invNum int, 
         amt money,
         state char(2))

Insert Into @risInv
Select 111, 111,1110,'FL'
Union All Select 111, 112,1011,'FL'
Union All Select 112, 113,1101,'FL'
Union All Select 112, 114,1211,'FL'
Union All Select 110, 115,1121,'CA'
Union All Select 110, 116,1112,'CA'
Union All Select 111, 117,1151,'CA'
Union All Select 120, 118,1511,'NY'
Union All Select 120, 119,2111,'NY'

Select State, count(invNum) InvoiceCount,  sum(amt) as GrossSum 
From @risInv
group by state with rollup

Results

State InvoiceCount GrossSum              
----- ------------ --------------------- 
CA    3            3384.0000      count and sum of invoices per state
FL    4            4433.0000        
NY    2            3622.0000        
NULL  9            11439.0000     total count and sum of invoices (total of the totals)

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-13 : 15:38:05

Declare @risInv table (
division int,
invNum int,
amt money,
state char(2))

Insert Into @risInv
Select 111, 111,1110,'FL'
Union All Select 111, 112,1011,'FL'
Union All Select 112, 113,1101,'FL'
Union All Select 112, 114,1211,'FL'
Union All Select 110, 115,1121,'CA'
Union All Select 110, 116,1112,'CA'
Union All Select 111, 117,1151,'CA'
Union All Select 120, 118,1511,'NY'
Union All Select 120, 119,2111,'NY'

Select case when grouping(State)=1 then 'Total' else state end State, count(invNum) InvoiceCount, sum(amt) as GrossSum
From @risInv
group by state with rollup

kpowell
Starting Member

4 Posts

Posted - 2004-10-13 : 16:03:36

Turns out I do not need totals of totals, this is probably a dumb question, but what is rollup? I have never heard nor have seen that term.

Thanks
Kim

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-13 : 22:34:41

it's pretty cool, i just read it in one of the forums here just a while ago. Read more in BOL, it's pretty straightforward. I think this will come in handy for us as we do lots and lots and lots of summary reports.

--------------------
keeping it simple...

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-14 : 00:56:49

Now that I've learned this, I already know about half-a-dozen recent sprocs where I need to go back and change to use ROLLUP. Would make the sprocs lot simpler. COOL !!!!

Thanks Tuenty

Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-10-14 : 06:02:26

Question: Will the rollup work if one of the values is a null?
Cool tool though!!

You can't teach an old mouse new clicks.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-10-14 : 06:04:12

Question: Will the rollup work if one of the values is a null?
Cool tool though!!

You can't teach an old mouse new clicks.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-14 : 06:29:05

sure does... NULL is treated as 0
just added this in tuenty's example...

Union All Select 111, 111,NULL,'FL'

Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-14 : 10:34:21

You Welcome Hemanth

I saw the cube and rollup functions some time ago but is until now that I have found a situation where this functions are usefull. Cube is a bit more powerfull but just as simple, see

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41203

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources