Group by problem again -tlbroadbent can u help 1

[Guest_imported]

I have a table which columns like "category", "Amount" and "A case column" and another calculated sum column and some other half a dozen columns.
the qry is like this :
select category,Amount ,value=(case when colx=coly then Y else N end),cola,colb..etc ..
I want to know how to get the SumOfColumnsByValue field.

i want an SQL query which will select ALL the columns of the table ..grouped by "category" subgrouped by value and giving Sum of "Amount" for that value.

Desired op shud be like this :

"Category" "Amount" "SumAmountsbyvalue" value
A 100 170 Y

A 50 170 y

A 20 170 Y

A 40 140 N
A 50 140 N
A 50 140 N


B 10 17 Y

B 5 17 y

B 2 17 Y

B 140 440 N
B 150 440 N
B 150 440 N


Can any one help me ?

 

[tlbroadbent]

Here is a solution. Let me know if you have any questions about it.

Select
a.Category, a.Amount, b.SumAmountByValue,
a.Value, a.ColA, a.ColB, a.ColC, ...
From
(Select
Category, Amount,
Value=Case When ColX=ColY Then 'Y' Else 'N' End,
ColA, ColB, ColC, ...
From table_name) As a
Inner Join
(Select
Category, sum(Amount) As SumAmountByValue,
Value=Case When ColX=ColY Then 'Y' Else 'N' End
From table1
Group By
Category,
Case When ColX=ColY Then 'Y' Else 'N' End) As b
On a.Category=b.Category
And a.Value=b.Value Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Guest_imported]

Thanks Terry ...it works fine