CODEWITHSUNDEEP
sqlfirebird
Chris
Chris

Reputation: 364

SQL query to count different records and print in 1 row

I have the following table and I am looking for a SQL query which counts the rewards for each name and also print the last date a specific reward type was earned.

This is my table:

   NAME   |   REWARD    |    DATE
----------+-------------+------------
  Chris   |    small    |  18.05.2014
  Chris   |    small    |  27.08.2015
  Chris   |    big      |  01.07.2014
  Tom     |    big      |  10.10.2016
  Tom     |    big      |  30.11.2017

The result should look like this:

   NAME   | BIG_REWARDS | SMALL_REWARDS | LAST_BIG_REWARD | LAST_SMALL_REWARD
----------+-------------+---------------+-----------------+-------------------
  Chris   |      1      |       2       |    01.07.2014   |    27.08.2015
  Tom     |      2      |       0       |    30.11.2017   |

I am using Firebird

Upvotes: 2

Views: 412

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175826

You could use conditional aggregation:

SELECT name,  
      SUM(CASE WHEN reward = 'big'   THEN 1 ELSE 0 END) AS BIG_REWARDS,
      SUM(CASE WHEN reward = 'small' THEN 1 ELSE 0 END) AS SMALL_REWARDS,        
      MAX(CASE WHEN reward = 'big'   THEN "DATE" END) AS LAST_BIG_REWARD,
      MAX(CASE WHEN reward = 'small' THEN "DATE" END) AS LAST_SMALL_REWARD
FROM tab
GROUP BY name;

Upvotes: 5

Related Questions