Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    1

    Unanswered: Group by for different column values

    Hi,

    I have 2 tables which have the data as follows:
    A:
    Id Reason Amount Subject RecordNo
    1 Gift 100 first 11
    2 Gift Reason 200 second 12
    3 Gift Reason 100 first 11

    the result that I want is
    Reason Amount Subject
    ALL 200 first
    i used the following query

    select
    case when Grouping(B.Subject) <> '' then 'ALL' else B.Subject End as Subject ,
    case when Grouping(B.reason) <> '' then 'ALL' else B.Reason END as Reason,
    SUM(B.amount) as amount
    from B
    where B.RecordNo = 11
    group by grouping sets (B.Reason,B.Subject)

    but it's giving me 3 rows in return.

    i want only one summation row.

    how can i get that?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This will still give you 3 rows:
    Code:
    select
    	COALESCE(B.Subject, 'ALL') as Subject ,
    	COALESCE(B.reason, 'ALL') as Reason,
    	SUM(B.amount) as amount
    from #DaTable as B
    where B.RecordNo = 11
    group by grouping sets (B.Reason, B.Subject)
    This is the only way you get 1 row as result set:
    Code:
    select
    	'ALL' as Subject ,
    	'ALL' as Reason,
    	SUM(B.amount) as amount
    from #DaTable as B
    where B.RecordNo = 11
    I don't know why you used GROUPING SETS if you only want 1 row.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •