CODEWITHSUNDEEP
sqlsql-servert-sql
ltsai
ltsai

Reputation: 757

Grouping By Alias names

I am trying to group my station codes as shown below however my result set keeps outputting everything and others multiple times. I want to group everything by my alias names however for my station others it doesn't seem like it is grouping it.

WITH
        station as (
        SELECT
         CASE
            WHEN t.station='AB' THEN 'AB'
            WHEN t.station='AS' THEN 'AS'
            WHEN t.station='KF' THEN 'KF'
            WHEN t.station='PR' THEN 'PR'
            WHEN t.station='SV' THEN 'SV'
            WHEN t.station='RE' THEN 'RE'
            WHEN t.station='TVR' OR t.station='TDT' THEN 'TDT'
            ELSE 'Others'
        END AS 'station'
        FROM t

    )
        porders as (
        SELECT 
        CASE
            WHEN t.station='AB' THEN 'AB'
            WHEN t.station='AS' THEN 'AS'
            WHEN t.station='KF' THEN 'KF'
            WHEN t.station='PR' THEN 'PR'
            WHEN t.station='SV' THEN 'SV'
            WHEN t.station='RE' THEN 'RE'
            WHEN t.station='TVR' OR t.station='TDT' THEN 'TDT'
            ELSE 'Others'
        END AS 'station',
        COUNT(*) AS 'p_orders'
        FROM a, t, v, station
        WHERE a.psn = t.psn
          AND t.highest_psn = 0
          AND v.detailno_i = a.detailno_i
          AND t.station!=''
          AND a.status=1
          AND v.order_type='P'
          GROUP BY t.station

)
        SELECT station.station, porders.p_orders
        FROM station join porders on(station.station=porders.station)

Output:

station|porders
---------------
 AB       2
 AS       4
 KF       3
 PR       10
 SV       2
 RE       20
 TDT      10
 Others    1
 Others    2
 Others    3

Desired Output:

station|porders
---------------
 AB       2
 AS       4
 KF       3
 PR       10
 SV       2
 RE       20
 TDT      10
 Others    6

Upvotes: 0

Views: 67

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

This isn't an answer, just a note that you can simplify your case statement a lot:

    CASE WHEN t.station in ('AB', 'AS', 'KF', 'PR', 'SV', 'RE') THEN t.station
         WHEN t.station in ('TVR', 'TDT') THEN 'TDT'
         ELSE 'Others'
    END

Upvotes: 2

Conrad Frix
Conrad Frix

Reputation: 52645

The results you're getting is due to the fact that you have three t.station values that get mapped to Other The statement GROUP BY t.station doesn't care that they get projected to Other in your Select.

To fix this you need to group by your case instead of the untranslated value t.station

GROUP BY
       CASE
            WHEN t.station='AB' THEN 'AB'
            WHEN t.station='AS' THEN 'AS'
            WHEN t.station='KF' THEN 'KF'
            WHEN t.station='PR' THEN 'PR'
            WHEN t.station='SV' THEN 'SV'
            WHEN t.station='RE' THEN 'RE'
            WHEN t.station='TVR' OR t.station='TDT' THEN 'TDT'
            ELSE 'Others'
        END

Upvotes: 2

Gerrat
Gerrat

Reputation: 29690

For your last two lines, how about:

SELECT station.station, SUM(porders.p_orders) as tot_porders
FROM station join porders on(station.station=porders.station)
GROUP BY station.station

Upvotes: 0

Related Questions