Multiple joins on same table giving duplicate data

Question

Here is the sale_order table which contains CCNID

sale_orderid      client_channel_nameid
1                 1
2                 1
3                 2
4                 2
5                 2
6                 2
7                 1
8                 1

sale_order_item Table has sale_orderid as a foreign key

sale_order_itemid       sale_orderid     order_date                     selling_price
42219                   1                2018-03-21 00:00:00            200 
28948                   2                2018-03-21 16:17:55            100
42220                   3                2018-03-21 00:00:00            300
13194                   4                2018-03-21 13:33:58            400
42839                   5                2018-03-20 07:54:29            550
42840                   6                2018-03-20 07:58:20            600
42086                   7                2018-03-20 00:00:00            700
11691                   8                2018-03-20 05:32:31            500

And I want to get the sum of price of soid of 21 and 20 dates in different columns grouped by CCNID

client_channel_nameid   21      20
1                       300    1200
2                       700    1150

I am joining Sale order twice which is giving me wrong results

select  ccn.client_channel_nameid, 
        round (sum(soi.selling_price)), 
        round(sum(soi1.selling_price)) 
from app.client_channel_name ccn 
  join app.sale_order so on so.client_channel_nameid =     ccn.client_channel_nameid
  inner join app.sale_order_item soi on soi.sale_orderid = so.sale_orderid
  join app.sale_order so1 on so1.client_channel_nameid = ccn.client_channel_nameid
  inner join app.sale_order_item soi1 on soi1.sale_orderid = so1.sale_orderid
where ccn.clientid = 1 
  and to_char(soi.order_date, 'DD-MM-YYYY') = '20-03-2018' 
  and to_char(soi1.order_date, 'DD-MM-YYYY') = '21-03-2018'
group by client_channel_nameid;

Answer 1

Below query produce the desired result. From your above tried solution and question you asked I think you are looking for exactly 21 and 20 dates. The below will need slight changes with extra filters for add more dates ex.22,23,24...

with sale_order(
  sale_orderid, client_channel_nameid
) as (
  select
    *
  from
    (
      values
        (1, 1),
        (2, 1),
        (3, 2),
        (4, 2),
        (5, 2),
        (6, 2),
        (7, 1),
        (8, 1)
    ) as x(
      sale_orderid, client_channel_nameid
    )
),
sale_order_item(
  sale_order_itemid, sale_orderid,
  order_date, selling_price
) as (
  select
    *
  from
    (
      values
        (
          42219, 1, '2018-03-21 00:00:00' :: timestamp,
          200
        ),
        (
          28948, 2, '2018-03-21 16:17:55' :: timestamp,
          100
        ),
        (
          42220, 3, '2018-03-21 00:00:00' :: timestamp,
          300
        ),
        (
          13194, 4, '2018-03-21 13:33:58' :: timestamp,
          400
        ),
        (
          42839, 5, '2018-03-20 07:54:29' :: timestamp,
          550
        ),
        (
          42840, 6, '2018-03-20 07:58:20' :: timestamp,
          600
        ),
        (
          42086, 7, '2018-03-20 00:00:00' :: timestamp,
          700
        ),
        (
          11691, 8, '2018-03-20 05:32:31' :: timestamp,
          500
        )
    ) as x(
      sale_order_itemid, sale_orderid,
      order_date, selling_price
    )
)
select
  client_channel_nameid,
  sum(selling_price) filter (where to_char(order_date, 'dd-mm-yy') = '21-03-2018') as date_21,
  sum(selling_price) filter (where to_char(order_date, 'dd-mm-yy') = '20-03-2018') as date_20
from
  sale_order so
  join sale_order_item soi on soi.sale_orderid = so.sale_orderid
group by
  so.client_channel_nameid

Answer 2

You can group the data by CCNID and then only sum selling_price when the order_date day is the 21 or 20.

SELECT client_channel_nameid
     , SUM(CASE
               WHEN EXTRACT(day FROM order_date) = 21 THEN selling_price
               ELSE 0
             END) AS "21"
     , SUM(CASE
               WHEN EXTRACT(day FROM order_date) = 20 THEN selling_price
               ELSE 0
             END) AS "20"
FROM   sale_order so
JOIN   sale_order_item soi
  ON soi.sale_orderid = so.sale_orderid
GROUP  BY so.client_channel_nameid