sum sum help - 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)

sum sum help

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-01-07 : 11:29:45

I have following sample dataset. I want to get a sum on amt =500, the "right" answer, but my T-SQL below gave me 700 or 300 depending on I inculde keyword distinct or not.

chkNo/amt/issueDate/InvoiceNo/InvAmt
1234/200/2005-01-06/111/50
1234/200/2005-01-06/112/150
1235/200/2005-01-06/NULL/NULL
1236/100/2005-01-06/3/100

select count(distinct chkno), sum(distinct amt) from checkregister
group by issuedate

I modified it to have a sub sum, but it errors out.

select count(distinct chkno), sum
(select sum(distinct amt) from checkregister group by chkno)
from checkregister group by issuedate

I'd appreciate any help to get me the right answer.

Here is the DDL.

use pub
go
CREATE TABLE [dbo].[CheckRegister] (
[chkNo] [char] (10) NULL ,
[amt] [money] NULL ,
[issueDate] [datetime] NULL ,
[InvoiceNo] [char] (10) NULL ,
[InvAmt] [money] NULL
) ON [PRIMARY]
GO

insert into checkregister values(1234,200,2005-01-06,111,50)
insert into checkregister values(1234,200,2005-01-06,112,150)
insert into checkregister values(1235,200,2005-01-06,null,null)
insert into checkregister values(1236,100,2005-01-06,3,100)

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-07 : 13:37:50

How did you come up with 500 as the right answer? What makes it right? You haven't explained how you came up with that.

And please see the first post in this thread (page 2) about DISTINCT:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44320&whichpage=2

Tara

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-07 : 13:43:06

The problem is that your table is denormalized.

You need to split it into two tables, one for checks and one for invoices. Then your queries will work as you expect them to.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-01-07 : 14:00:12

To Tara,
They want to see a total count of number of check as well as total amount from those checks.

To aMachanic,
Yes, you are right, but unfortunately, normalization is not an option.

I guess I should try temp table. Thanks!

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-07 : 14:02:49

Don't bother with a temp table...

select count(chkno), sum(amt)
from checkregister
where invoiceno =
(select min(invoiceno)
from checkregister c1
where c1.chkno = checkregister.chkno)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-07 : 14:04:51

If you are just removing one of the 1234s, then GROUP BY chkNo as well and take one of the amts, so you can use MAX or MIN to do that.

Tara

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-01-07 : 14:34:07

Yah, temp table is not going to work since this is the second part of a union join. The first part is the detail.

I need to twist on Amachanic's code because as it is, I only got 2 as chkNo count instead of 3 due to nall value in invoiceNo.

As to Tara's suggestion, I am a little bit slow on how min/max and group by been used here. Did you mean in Amachanic's code or one of mine?

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-07 : 14:36:04

You could try:

select count(chkno), sum(amt)
from checkregister
where invoiceno =
(select min(invoiceno)
from checkregister c1
where c1.chkno = checkregister.chkno)
OR invoiceno IS NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-07 : 14:54:26

Here's what I came up with:



CREATE TABLE [dbo].[CheckRegister] (
[chkNo] [char] (10) NULL ,
[amt] [money] NULL ,
[issueDate] [datetime] NULL ,
[InvoiceNo] [char] (10) NULL ,
[InvAmt] [money] NULL 
) ON [PRIMARY]
GO

insert into checkregister values(1234,200,2005-01-06,111,50)
insert into checkregister values(1234,200,2005-01-06,112,150)
insert into checkregister values(1235,200,2005-01-06,null,null)
insert into checkregister values(1236,100,2005-01-06,3,100)

select 
	(select count(distinct chkno) from checkregister), 
	sum(amt)
from (select max(amt) as amt from checkregister group by chkno) t

drop table checkregister

Tara

VIG
Yak Posting Veteran

86 Posts

Posted - 2005-01-07 : 17:18:51

select count(t.chkno),sum(t.amt)
from
(select distinct chkno,amt from checkregister) t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources