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:
JavaScript
x
7
1
CREATE TABLE summary AS
2
SELECT marking,area,division,status,
3
SUM(CAST (amount AS INT)) AS amount,
4
count(*) FROM final_output
5
WHERE marking = 'A1'
6
GROUP BY marking,area,division,status
7
And below is the table created:
JavaScript
1
19
19
1
| Marking| Area | Division | Status | Amount | Count |
2
3
| A1 | NORTH | N1 | Status1 | 12314 | 1000 |
4
| A1 | NORTH | N1 | Status2 | 42342 | 2200 |
5
| A1 | SOUTH | S2 | Status1 | 1314 | 1314 |
6
| A1 | CENTRAL | C1 | Status1 | 314 | 157 |
7
| A1 | EAST | E1 | Status1 | 55555 | 1211 |
8
| A1 | EAST | E2 | Status1 | 23423 | 1324 |
9
| A1 | SOUTH | S2 | Status2 | 100 | 50 |
10
| A1 | CENTRAL | C2 | Status1 | 4000 | 1324 |
11
| A1 | WEST | W1 | Status1 | 7000 | 3333 |
12
| A1 | NORTH | N2 | Status1 | 213212| 1000 |
13
| A1 | CENTRAL | C3 | Status1 | 212 | 77 |
14
| A1 | WEST | W2 | Status1 | 12314 | 4444 |
15
| A1 | EAST | E2 | Status2 | 1231 | 2222 |
16
| A1 | SOUTH | S2 | Status3 | 314 | 1111 |
17
| A1 | CENTRAL | C2 | Status2 | 1920 | 9000 |
18
| A1 | WEST | W1 | Status2 | 30022 | 4444 |
19
I am trying to create a query on this table to further create a summary shown below:
JavaScript
1
14
14
1
| Status |
2
| Status 1 | Status 2 |Total Count|Total Amount|
3
|Area | Amount | Count | Amount | Count | | |
4
|**NORTH** | 15000 | 150 | 20000 | 2000 | 2150 | 35000 |
5
|N1 | 5000 | 50 | 12000 | 1000 | 1050 | 17000 |
6
|N2 | 10000 | 100 | 8000 | 1000 | 1100 | 18000 |
7
|**EAST** | 50000
8
|N1 | | . | .. | . |
9
|N2 | | . | .. | . |
10
|**CENTRAL**|
11
|N1 | | . | .. | . |
12
|N2 | | . | .. | . |
13
|GRAND TOTAL|column tot| ..
14
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.
JavaScript
1
9
1
CREATE TABLE .
2
IN (
3
SELECT marking,area,division,status,
4
SUM(CAST (amount AS INT)) AS amount,
5
count(*) FROM final_output
6
WHERE marking = 'A1'
7
GROUP BY marking,area,division,status)
8
9
Thank you
Advertisement
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:
JavaScript
1
13
13
1
select Marking
2
, Area
3
, Division
4
, sum(amount) filter (where status = 'Status1') Status1Amount
5
, count(*) filter (where status = 'Status1') Status1Count
6
, sum(amount) filter (where status = 'Status2') Status2Amount
7
, count(*) filter (where status = 'Status2') Status2Count
8
, sum(amount) TotalAmount
9
, count(*) TotalCount
10
from final_output
11
where Marking = 'A1' --< you can remove where clause to get the result for all the markings
12
group by rollup( marking, Area , Division)
13
group by rollup
gives you grand total as well.