Difficult postgreSQL query

Question

I have two tabels

user

    id |  name  | sex | birth
    ---+--------+-----+------
    1  |  User1 | 0   | 2007-07-12
    2  |  User2 | 0   | 1988-05-10
    3  |  User3 | 1   | 2000-01-11
    4  |  User4 | 1   | 1999-10-10

And another tabel

order

id | user_id | price | order_date  
---------------------------------------------
1  | 3       |  5    | 2017-07-10 08:01:00.000000
2  | 3       |  6    | 2017-07-11 09:01:00.000000
3  | 1       |  8    | 2017-07-12 10:01:00.000000
4  | 2       |  10   | 2017-07-13 11:01:00.000000
5  | 4       |  100  | 2017-07-14 12:01:00.000000
6  |         |  58   | 2017-07-15 13:01:00.000000

I need result tabel like this

order

          |age    |   men_pr | women_pr| 
          |-----------------------------
          |<18    |     8    |         | 
          |18-50  |     10   | 111     |  
          |>50    |          |         |

with gradation by date

WHERE  
      order.order_date  >= '2017-07-01 08:01:00.000000'
      AND order.order_date  <= '2017-07-15 08:01:00.000000'

What have you tried, and where are you having difficulty?

rd_nielsen
– rd_nielsen

2017-08-02 15:37:52 +00:00
Commented Aug 2, 2017 at 15:37 — rd_nielsen
– rd_nielsen, Commented Aug 2, 2017 at 15:37

Julia Leder · Accepted Answer · 2017-08-02 15:43:12Z

1

First you want to transform the birthdates into age categories:

SELECT id, 
(case when date_part('year',age(birth)) < 18 then '<18' 
      when date_part('year',age(birth)) > 50 then '>50' 
      else '18-50' end) as 
age, sex from g_user;

which will result in this:

id |  age  | sex
----+-------+-----
  1 | <18   |   0
  2 | 18-50 |   0
  3 | <18   |   1
  4 | <18   |   1
(4 rows)

We will use that as in inner query so that we can later group by age category. Next we need a table that will have all age categories in it (since we want that in the output but we do not have any users over 50 years old):

select '<18' as age union select '18-50' union select '>50';

Now to put it all together and then sum up the prices depending on gender:

select a.age, 
       nullif(sum(case when sex = 0 then price else 0 end),0) as men_pr, 
       nullif(sum(case when sex = 1 then price else 0 end),0) as women_pr
from (select '<18' as age union select '18-50' union select '>50' ) as a 
left join (SELECT id, (case when date_part('year',age(birth)) < 18 then '<18' 
      when date_part('year',age(birth)) > 50 then '>50' else '18-50' end) as 
age, sex from g_user) u on a.age = u.age
left join g_order on u.id = user_id
group by a.age;

We need to use the left join here to make sure that all the categories show up regardles or whether or not there are users of that age present in data.

This is the result

  age  | men_pr  | women_pr
-------+---------+----------
 <18   |       8 |      111
 18-50 |      10 |
 >50   |         |
(3 rows)

answered Aug 2, 2017 at 15:43

Julia Leder

8265 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taras Oleksiuk Over a year ago

thanks work almost perfect. But when I add` WHERE order.order_date >= '2017-07-01 08:01:00.000000' AND order.order_date <= '2017-07-15 08:01:00.000000'` disappear row without data

Collectives™ on Stack Overflow

Difficult postgreSQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related