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.
精彩评论