As a n00b in SQL, can you help me out.

In SQL (or Google's BigQuery) current table :

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 :

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 :

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