As a n00b in SQL, can you help me out.
In SQL (or Google's BigQuery) current table :
Column A | Column B |
---|---|
2021-12-31 | null |
null | 2022-01-23 |
My query which is wrong:
select
count(ColA) as A,
count(ColB) as B,
FORMAT_DATE('%Y %m', ColA) as month_year,
from myTable
group by month_year
so i've got so far :
month_year | Count A | Count B |
---|---|---|
2021-12 | 1 | 0 |
the output i'd like to do a simple count, aggregated year month. As you can see i will be missing some counts on col B since we are using the 'array' of dates from ColA.
So how can I make a proper count on both columns combined as the 'aggregate array' for the year month grouping.
i want :
month_year | Count A | Count B |
---|---|---|
2021-12 | 1 | 0 |
2022-01 | 0 | 1 |
CodePudding user response:
Try the following:
select FORMAT_DATE('%Y-%m', coalesce(column_a, column_b)) month_year
, count(column_a) as a
, count(column_b) as b
from sample_data
group by month_year
The coalesce function allows you to return a value that is not null. As a caveat this solution may not work as expected if both columns have a value for both columns that are in different months.
CodePudding user response:
You can try this (MS SQL)
select
COALESCE((FORMAT ( ColA , 'yyyy-MM' )), (FORMAT ( ColB , 'yyyy-MM' ))) AS month_year,
count(ColA) AS [Count A],
count(ColB) AS [Count B]
from myTable
group by ColA,ColB
DEMO LINK https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0fe2e9995a7040f8aaeecd8339012ee3