Below I have an example of a query that I am trying to perform. The INPUT is the table that I have access to. The OUTPUT is how I would like to organize the data. I am trying to group the data by the date and have an output for the number ups and the number of downs, so the output will give a string, int, int.

This query is a little more advanced than I've done before, but I think I will need something similar to this:

SELECT date, COUNT(*), COUNT(*) FROM Input GROUP BY date

I am not sure how to format the COUNTs to look for only UP or DOWN on a particular date. Could someone help point me in the right direction

INPUT

OUTPUT

CodePudding user response：

You can try sum with a conditional case expression

select date, 
  Sum(case when status='Up' then 1 else 0 end) NumUps, 
  Sum(case when status='Down' then 1 else 0 end) NumDowns
from t
group by date;