How Do You SELECT Both With and Without a WHERE in a Query?

I have a single table, my_table, with data like the following:

+---------------------------+
|Accesses | Fruit   | Level |
+---------------------------+
|      5  | Apple   |     2 |
|      2  | Banana  |     3 |
|      2  | Apple   |     1 |
|      6  | Cherry  |     4 |
|     12  | Apple   |     4 |
|      9  | Banana  |     1 |
|      1  | Durian  |     2 |
+---------------------------+

I’m trying to write a query that would both sum the number of accesses GROUP BY’d fruit and the total number of access for each fruit, but only the accesses for levels less than 4. A result for that query would be:

+---------------------------|-------------- +
|Fruit   | FilteredAccesses | TotalAccesses |
+---------------------------+---------------+
|Apple   |                7 |            19 | 
|Banana  |               11 |            11 |
|Durian  |                1 |             1 |
+-------------------------------------------+

I tried the following self join, but didn’t get the expected results:

SELECT A.Fruit, SUM(A.Accesses) AS FilteredAccesses, SUM(B.Accesses) As TotalAccesses
FROM my_table A, my_table B
WHERE (A.Fruit = B.Fruit) AND (A.Level < 4)
GROUP BY A.Fruit

Is there something that I’m missing? Could this even be done with a self join?

Answer

Use conditional aggregation:

SELECT A.Fruit, SUM(CASE WHEN A.LEVEL < 4 THEN A.Accesses ELSE 0 END) AS FilteredAccesses,
      SUM(A.Accesses) As TotalAccesses
FROM my_table A
GROUP BY A.Fruit

Advertisement

Answer