CODEWITHSUNDEEP
sqloracle-database
Ram Viswanathan
Ram Viswanathan

Reputation: 201

sql grouping within a group of results

I have a table called 'payments' with the following data 

+---------------------------------------+
|   status    |  currency   |  amount   |  
+---------------------------------------+
| ------------------------------------- |
| paid        |  USD        |  10       |  
| pending     |  USD        |  20       | 
| processing  |  GBP        |  30       | 
| paid        |  GBP        |  40       | 
| paid        |  EUR        |  50       | 
| pending     |  USD        |  60       | 
| processing  |  EUR        |  70       | 
+----------------------------------------+

I need to write a query that will give me results in the following format

Paid : 3 
Processing: 2
Pending: 2

I am able to do this using the following query

select status, count(status)
from payments
group by status;

Now I also need to know the sum of amounts grouped by currency for each of these high level groups. For example,

For the Paid status that has 3 records, I also need 

USD: 10
GBP: 40
EUR: 50

How can I do this ?

Upvotes: 1

Views: 97

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

The first query should be something like:

SELECT INITCAP(STATUS) || ' : ' || COUNT(*) AS OUTPUT_TEXT
  FROM PAYMENTS
  GROUP BY STATUS
  ORDER BY CASE STATUS
             WHEN 'paid' THEN 1
             WHEN 'processing' THEN 2
             WHEN 'pending' THEN 3
             ELSE 4
           END

The second query should be

SELECT CURRENCY || ': ' || SUM(CURRENCY) AS OUTPUT_TEXT
  FROM PAYMENTS
  GROUP BY CURRENCY
  ORDER BY CASE CURRENCY
             WHEN 'USD' THEN 1
             WHEN 'GBP' THEN 2
             WHEN 'EUR' THEN 3
             ELSE 4
           END

Upvotes: 0

GitItGotItGood
GitItGotItGood

Reputation: 61

select status, currency, sum(amount)
from payments
group by status, currency;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269533

I think you want:

select status, currency, count(*), sum(amount)
from payments
group by grouping sets ( (status, currency), (status) )

Upvotes: 0

Related Questions