How to create a summary table with subheadings in SQL?

I am using PostgreSQL to create summaries from data using a python script. I had large amounts of data in my SQL table and using the following query I was able to get the required data. Below is my query:

CREATE TABLE summary AS
SELECT marking,area,division,status,
SUM(CAST (amount AS INT)) AS amount,
count(*) FROM final_output
WHERE marking = 'A1'
GROUP BY marking,area,division,status

And below is the table created:

| Marking| Area    | Division | Status  | Amount | Count |

|  A1    | NORTH   | N1       | Status1 |  12314 | 1000  |
|  A1    | NORTH   | N1       | Status2 |  42342 | 2200  |
|  A1    | SOUTH   | S2       | Status1 |  1314  | 1314  |
|  A1    | CENTRAL | C1       | Status1 |  314   | 157   |
|  A1    | EAST    | E1       | Status1 |  55555 | 1211  |
|  A1    | EAST    | E2       | Status1 |  23423 | 1324  |
|  A1    | SOUTH   | S2       | Status2 |  100   | 50    |
|  A1    | CENTRAL | C2       | Status1 |  4000  | 1324  |
|  A1    | WEST    | W1       | Status1 |  7000  | 3333  |
|  A1    | NORTH   | N2       | Status1 |  213212| 1000  |
|  A1    | CENTRAL | C3       | Status1 |  212   | 77    |
|  A1    | WEST    | W2       | Status1 |  12314 | 4444  |
|  A1    | EAST    | E2       | Status2 |  1231  | 2222  |
|  A1    | SOUTH   | S2       | Status3 |   314  | 1111  |
|  A1    | CENTRAL | C2       | Status2 |  1920  | 9000  |
|  A1    | WEST    | W1       | Status2 |  30022 | 4444  |

I am trying to create a query on this table to further create a summary shown below:

            | Status                                                      |
            |      Status 1    |      Status 2   |Total Count|Total Amount|
|Area       | Amount   | Count |  Amount | Count |           |            |
|**NORTH**  | 15000    | 150   | 20000   | 2000  | 2150      | 35000      |
|N1         | 5000     | 50    | 12000   | 1000  | 1050      | 17000      |
|N2         | 10000    | 100   | 8000    | 1000  | 1100      | 18000      |
|**EAST**   | 50000
|N1         | ......   | ....  | .....    | ....  |    
|N2         | ......   | ....  | .....    | ....  |  
|**CENTRAL**|
|N1         |  ......  | ....  | .....    | ....  |      
|N2         |  ......  | ....  | .....    | ....  |    
|GRAND TOTAL|column tot|.............................

I want to make a query to create a summary like above. Is this possible using SQL and can I directly execute a query on my first query without creating the new table and directly creating the final summary? for ex.

CREATE TABLE ....
IN (
SELECT marking,area,division,status,
SUM(CAST (amount AS INT)) AS amount,
count(*) FROM final_output
WHERE marking = 'A1'
GROUP BY marking,area,division,status)

Thank you

Answer

you can get the values you want by this query , but the way you want to show it , should be done in the UI in your app:

select Marking
       , Area 
       , Division 
       , sum(amount) filter (where status = 'Status1') Status1Amount
       , count(*)    filter (where status = 'Status1') Status1Count
       , sum(amount) filter (where status = 'Status2') Status2Amount
       , count(*)    filter (where status = 'Status2') Status2Count
       , sum(amount) TotalAmount
       , count(*) TotalCount
from final_output
where Marking = 'A1' --< you can remove where clause to get the result for all the markings
group by rollup( marking, Area , Division)

group by rollup gives you grand total as well.

Advertisement

Answer