How to count number if special type of value by day in SQL?-CodePudding

I have a table in my database which looks like this:

time                              value_type     id
2020-05-16 10:55:33.000            upload        a11
2020-05-16 10:54:33.000            delete        a11
2020-05-15 10:52:18.000            save          b77
2020-05-15 10:51:24.000            upload        b77
2020-05-15 10:20:24.000            upload        b77

I want to get table with number of upload value_types per day (my time column is not per day as you see). So for this example it must look like:

day           upload_num
2020-05-16         1
2020-05-16         2

How should this SQL query look like? I dint understand turning time into days part especially.

CodePudding user response：

You seem to want count() with filtering:

select time::date as day, count(*) filter (where value_type = 'upload')
from t
group by day;

The above will return 0 on days that have data in the data but no uploads. You can also filter in the where clause to remove those entirely:

select time::date as day, count(*)
from t
where value_type = 'upload'
group by day;