How to join 3 records as 1 record base on a same date?-CodePudding

This are some sample queries I wrote:

SELECT 
    CAST(datecolumn AS DATE) AS DateColumn, 
    COUNT(*) AS count 
FROM 
    dbo.myTableName 
WHERE 
    status = 'stage1'
GROUP BY CAST(datecolumn AS DATE) ORDER BY DateColumn DESC;


SELECT 
    CAST(datecolumn AS DATE) AS DateColumn, 
    COUNT(*) AS count 
FROM 
    dbo.myTableName 
WHERE 
    status = 'stage2'
GROUP BY CAST(datecolumn AS DATE) ORDER BY DateColumn DESC;

This is the output from the 1st query:

DateColumn  count
------------------
2022-05-26  23
2022-05-25  51
2022-05-24  39
2022-05-23  55
2022-05-22  27
2022-05-21  90

and this is the output from the 2nd query:

DateColumn  count
-----------------
2022-05-26  31
2022-05-25  67
2022-05-24  38
2022-05-23  54
2022-05-22  28

I want to only have a single query that will output it like this

DateColumn  stage1count stage2count
-----------------------------------
2022-05-26  23            31
2022-05-25  51            67
2022-05-24  39            38
2022-05-23  55            54
2022-05-22  27            28

Thanks for answer

CodePudding user response：

Can you try this:

select cast(datecolumn as DATE) as DateColumn,
       sum(case when status = 'stage1' then 1 else 0 end) as stage1count,
       sum(case when status = 'stage2' then 1 else 0 end) as stage2count
from dbo.myTableName
where status in ('stage1', 'stage2')
group by cast(datecolumn as DATE)
order by DateColumn DESC

Another note: Most SQL systems treat datecolumn and DateColumn the same, so it is somewhat ambiguous which it is actually using in the group by and order by clauses. I think the order by is using the casted value in the select list, and the groupby might be using the base column (uncasted) but I'm not sure about that. If you want to avoid the ambiguity, you can use a delimited identifier "DateColumn" instead.