CODEWITHSUNDEEP
sqlsqliteaggregate-functionscase-when
Ron Buenavida
Ron Buenavida

Reputation: 13

Sqlite query aggregating

I'm looking to solve the following issue using Sqlite3. I would like to aggregate this data:

+--------+-------+----------+----------+--------+
| DRAWID | BETID | TICKETID |  STATUS  | AMOUNT |
+--------+-------+----------+----------+--------+
|      1 |     1 |        1 | OK       |      3 |
|      1 |     2 |        1 | OK       |      2 |
|      1 |     3 |        2 | CANCELED |      4 |
|      1 |     4 |        2 | CANCELED |      7 |
|      1 |     5 |        3 | OK       |      5 |
|      1 |     6 |        3 | OK       |      4 |
+--------+-------+----------+----------+--------+

To the following

+--------+-----------+-----------+--------------+----------+-------------+
| BetsOK | BetsNotOk | TicketsOk | TicketsNotOk | AmountOk | AmountNotOk |
+--------+-----------+-----------+--------------+----------+-------------+
|      4 |         2 |         2 |            1 |       14 |          11 |
+--------+-----------+-----------+--------------+----------+-------------+

To get the right grouping for TicketsOk I seem to be resulting to doing two subqueries and having to join. One subquery for Ok and another for NotOk like this 

(SELECT drawId, (DISTINCT ticketId) FROM Bets WHERE drawId =  AND userId = AND Status IN ("OK")) TblTicketsOk ...

Is there something possibly that exists in sqlite3 or in general sql that would be simpler. Can someone please help in writing the full sql statement to solve this?

Upvotes: 1

Views: 28

Answers (1)

D-Shih
D-Shih

Reputation: 46229

You don't need use join, try to use distinct and CASE WHEN in COUNT function.

SELECT COUNT(distinct(CASE WHEN STATUS = 'OK' then BETID end)) BetsOK,
       COUNT(distinct(CASE WHEN STATUS = 'CANCELED' then BETID end)) BetsNotOk,
       COUNT(distinct(CASE WHEN STATUS = 'OK' then TICKETID  end)) TicketsOk,
       COUNT(distinct(CASE WHEN STATUS = 'CANCELED' then TICKETID  end)) TicketsNotOk,
       SUM(CASE WHEN STATUS = 'OK' then AMOUNT else 0 end) AmountOk,
       SUM(CASE WHEN STATUS = 'CANCELED' then AMOUNT  else 0 end) AmountNotOk
FROM T

Sqlfiddle

[Results]:

| BetsOK | BetsNotOk | TicketsOk | TicketsNotOk | AmountOk | AmountNotOk |
|--------|-----------|-----------|--------------|----------|-------------|
|      4 |         2 |         2 |            1 |       14 |          11 |

Upvotes: 2

Related Questions