December 17, 2003 at 6:41 am
Hi all,
I have little bit confusion in generating a result set. I have followning values in my table tblXX
ColA ColB ColC
1 40 0
1 30 2
2 10 0
2 5 0
I want the sum of ColB for distinct ColA, ColC values, So I have written a query like
SELECT ColA,SUM(ColB) as SumB,ColC FROM tblXX
GROUP BY ColA,ColC
I got results as follows.
ColA SumB ColC
---- ---- -----
1 0 0
1 70 2
2 15 0
Here I want sum of ColB value for both of ColA values,But here it has given 0 for ColA=1 and ColC=0...
Canu U help on this.
Thanks,
Madhu
Madhu
Madhu
December 17, 2003 at 7:02 am
This is very strange. The query you mention should return the result as you describe it, not the result you mention.
Are you really sure about the source data and the result?
December 17, 2003 at 7:04 am
Try executing this, it should return the result as you expect it...
USE Pubs
GO
create table testgroup
(colA int,
colB int,
colC int)
GO
SET nocount on
GO
insert into testgroup values (1,40,0)
insert into testgroup values (1,30,2)
insert into testgroup values (2,10,0)
insert into testgroup values (2,5,0 )
GO
PRINT 'TestData'
select * from testgroup
PRINT 'Result'
select colA, sum(colB) as sumB, colC from testgroup group by colA, colC
GO
drop table testgroup
GO
December 17, 2003 at 7:13 am
There are some SQL Server bugs that will return incorrect results with aggregations, but most of these bugs have been corrected in subsequent service packs. None of the bugs would occur with such a simple example as yours, though, so I suspect that the actual data and query is much more complex. What version and service pack of SQL Server are you running? What is the actual DDL for the table and DML for the query?
--Jonathan
--Jonathan
December 17, 2003 at 9:34 am
Reminds me of playing Mastermind with my nephew. In one game I told him to take a close look at his scoring of my guesses, because there was no possible solution.
My recommendation is to take a close look at your data and perhaps data types. You probably posted a simplified version of your problem & perhaps simplified out the actual problem.
The only normal people are those you don't know well - Oscar Wilde
Data: Easy to spill, hard to clean up!
December 17, 2003 at 9:54 pm
SQL Service version in my system is
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
I have got results when I executed query given by NPeeters
colA sumB colC
----------- ----------- -----------
1 40 0
2 15 0
1 30 2
Thanks for your suggestions.
Madhu.
Madhu
Madhu
December 18, 2003 at 3:55 am
If you got the correct results from 'my' query, you see that the solution you posted is correct. I just added your data and your query to a single script.
Can you post the complete query and the exact source data in the tblXX? That might give us pointers to the problem.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply