SQL query to the required output-CodePudding

I need a desired output as below out of two input tables order tableusers table

id  order_date        id    username
1   2019-01-01        1 A
2   2019-01-01        2 B
3   2019-01-02        3 B
4   2019-01-03        4 A
5   2019-01-03        5 B

Desired Output

order_date  username    orders
2019-01-01  A          1
2019-01-02  A          0
2019-01-03  A          1

I tried with this query,

SELECT o. order_date as order_date, u.username as username,
       ISNULL (COUNT (username),0) AS orders
FROM Order O LEFT JOIN users U ON o.id=u.id
WHERE   u.username = ‘A’
GROUP BY o. order_date, u.username
ORDER BY o. order_date, u.username

Which give me this result

order_date  username    orders
  
2019-01-01  A          1
2019-01-03  A          1

I don't know how to bring this part in the result "2019-01-02 A 0"

could anyone please help me with the query, Thanks in advance

CodePudding user response：

You can do:

select d.order_date, 'A' as username, coalesce(cnt, 0) as orders
from (select distinct order_date as order_date from orders) d
left join (
  select o.order_date, count(*) as cnt
  from orders o 
  join users u on u.id = o.id 
  where u.username = 'A'
  group by o.order_date
) t on t.order_date = d.order_date
order by d.order_date

Result:

 order_date  username  orders 
 ----------- --------- ------ 
 2019-01-01  A         1      
 2019-01-02  A         0      
 2019-01-03  A         1

See running example at db<>fiddle.

CodePudding user response：

You can use the query below in which includeAllUsers (using CROSS JOIN) allows you to include 'A' --without put it in the SELECT clause--, and StrictMatching gives you the real dataset using matching ID between the two tables Order & Users (by the way, you have really to change the name of your order table in Orders or other words, because ORDER is a reserved word).

select includeAllUsers.Order_Date, 
       coalesce(StrictMatching.username,includeAllUsers.username) User_Name,
       count(distinct StrictMatching.username) Total_Orders  
from (select o.order_date, u.username, u.id from orders o cross join users u) includeAllUsers 
left join (select o.order_date, u.username,o.id from orders o join users u on o.id=u.id) StrictMatching 
on includeAllUsers.order_date = StrictMatching.order_date and StrictMatching.username='A'
where includeAllUsers.username='A'
group by 
includeAllUsers.order_date, StrictMatching.username, includeAllUsers.username;

By combining includeAllUsers and StrictMatching and filtering by StrictMatching.username='A' (in the criterium of the JOIN clause) et again by includeAllUsers.username='A' (in WHERE clause), you get the correct result.

Here is a link to verify