SQL some selections into one (or get two colums from one)

I use PostgreSql, I have two tables (for example) Let table1 will contain stores, there are 2 types ‘candy store’ and ‘dental store’.
Each row contains information about a customer’s purchase in a particular store
In result i want to get money from each type of store group by id and the last date of purchase.
Money from candy stores start sum since 2016, but money from dental stores start sum from 2018
table1:

+----+---------+------------------+-------+
| id | store   | date of purchase | money |
| 1  | store 1 | 2016-01-01       | 10    |
| 1  | store 5 | 2018-01-01       | 50    |
| 2  | store 2 | 2017-01-20       | 10    |
| 2  | store 3 | 2019-02-20       | 15    |
| 3  | store 2 | 2017-02-02       | 20    |
| 3  | store 6 | 2019-01-01       | 60    |
| 1  | store 1 | 2015-01-01       | 20    |
+----+---------+------------------+-------+

table2 :

+---------+--------+
| store   | type   |
| store 1 | candy  |
| store 2 | candy  |
| store 3 | candy  |
| store 4 | dental |
| store 5 | dental |
| store 6 | dental |
+---------+--------+

I want my query to return a table like this:

+----+---------------+-----------------+---------------+-----------------+
| id | money( candy) | the last date c | money(dental) | the last date d |
| 1  | 10            | 2016-01-01      | 50            | 2018-01-01      |
| 2  | 25            | 2019-02-20      | -             | -               |
| 3  | 20            | 2017-02-02      | 60            | 2019-01-01      |
+----+---------------+-----------------+---------------+-----------------+

Answer

if I understand correctly , this is what you want to do :

select id 
   , sum(money) filter (where ty.type = 'candy') candymoney
   , max(purchasedate) filter (where ty.type = 'candy') candylastdate
   , sum(money) filter (where ty.type = 'dental') dentalmoney
   , max(purchasedate) filter (where ty.type = 'dental') dentallastdate
from table t
join storetype table st on t.store = ty.store 
group by id

Advertisement

Answer