开发者

Group 2 rows into 1 in Oracle using SQL

I have a table which has data like

state   total   

A       3   

B       6

C       2 

D       7

E       4

I need to generate a table from this that has total of A & B (true) t开发者_Go百科ogether and C, D, E (False) together

Result Table

Status     Total

True        9 (sum of A and B )

False       13 (sum of C, D, E)

Any ideas how to do this using SQL? I am doing this in Oracle


SELECT  nstate, SUM(total)
FROM    (
        SELECT  DECODE(state, 'A', 'True', 'B', 'True', 'False') AS nstate, total
        FROM    mytable
        )
GROUP BY
        nstate


I'd use a UNION query

SELECT 'True' AS Status, SUM(total) AS Total
FROM table
WHERE state IN ('A', 'B')
UNION
SELECT 'False' AS STATUS, SUM(total) AS Total
FROM table
WHERE state IN ('C', 'D', 'E')
ORDER BY Status DESC;

You may need to group by Status on each query but I'm not sure as the column is virtual / static / scalar


I like CASE - I think it's easier to interpret than DECODE:

CREATE TABLE RESULT_TABLE AS
  SELECT STATE, SUM(TOTAL) AS TOTAL
    FROM (SELECT CASE STATE
                    WHEN 'A' THEN 'True'
                    WHEN 'B' THEN 'True'
                    ELSE 'False'
                 END AS STATE,
                 TOTAL
            FROM MY_TABLE) 
  GROUP BY STATE;

Share and enjoy.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜