CODEWITHSUNDEEP
sqlsql-server
Fadly
Fadly

Reputation: 191

Grouping with order in complex data

I have data like this

Name        valuta         price        Type    Type2
A            USD            10          Acc       1
B            USD            30          Acc       2
C            SGD            20          Acc       3
D            SGD            05          Acc       4
A            SGD            35          Acc       1
C            SGD            05          Acc       3
B            USD            50          Rej       2

Grouping based on Name, valuta and type. (type2 always have a unique value based on name) and sum the price when have the same group condition, the last step is to order based the highest price followed by same group condition.

And after process (Group & Order), the output should be like this:

Name        valuta         price        Type    Type2
B            USD            50          Rej       2
B            USD            30          Acc       2
A            SGD            35          Acc       1
A            USD            10          Acc       1
C            SGD            25          Acc       3
D            SGD            05          Acc       4

I tried use cte because pipeline process design seems clear to me but the result is wrong.

WITH Cte_Process1 AS
(
   SELECT
      Name,
      valuta,
      SUM(price) AS price,
      Type,
      Type2
   FROM table1
   GROUP BY 
      Name,
      valuta,
      price,
      Type,
      Type2
)
SELECT * FROM Cte_Process1 ORDER BY price

Upvotes: 1

Views: 75

Answers (3)

Joe Shark
Joe Shark

Reputation: 688

I know you said Type2 is unique based on Name, but you still need to include it in the GROUP BY (or else use a case statement to derive the value, or JOIN to a lookup table).

The following should give you the correct results.

SELECT Name, Valuta, SUM(Price), Type, Type2
FROM Table
GROUP BY Name, Valuta, Type, Type2

Upvotes: 0

forpas
forpas

Reputation: 164139

You can do it with MAX() window function in the ORDER BY clause:

select Name, valuta, sum(price) price, type, type2
from tablename
group by Name, valuta, type, type2
order by max(sum(price)) over (partition by Name) desc, 
         Name,
         sum(price) desc

See the demo.
Results:

> Name | valuta | price | type | type2
> :--- | :----- | ----: | :--- | ----:
> B    | USD    |    50 | Rej  |     2
> B    | USD    |    30 | Acc  |     2
> A    | SGD    |    35 | Acc  |     1
> A    | USD    |    10 | Acc  |     1
> C    | SGD    |    25 | Acc  |     3
> D    | SGD    |     5 | Acc  |     4

Upvotes: 3

Vignesh Mohan
Vignesh Mohan

Reputation: 1

For the above question, following query will work: (Assume table name - data)

select Name , valuta , sum(price) as price , Type, Type2 
from data 
group by 1,2,4,5 
order by sum(price) desc ;

Upvotes: 0

Related Questions