Post reply

Group By........

  • December 17, 2003 at 6:41 am

    #101812

    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

    #486651

    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

    #486653

    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

    #486656

    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

    #486726

    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

    #486868

    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

    #486894

    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