Is it valid to use sum(*) for all columns and group them by date time?

Question

I want to get sum of columns values and group them by half year window, but number of columns too many, which I want to automate such painful things with sql logic. since we can do count() for table, can we do sum() for getting sum of each columns values in big query standard sql? based on my intuition, I came up my sql but it didn't produce expected output. Can anyone suggest possible workaround ont this?

objective:

I just want to get sum of each column values then group by id, year, and half-year.

my attempt and reproducible data

here is my input table schema and values (Note that for making this minimal example, I just used sql server), here is dbfiddle

here is my attempt:

with tb1(
    select res.*
    from (
        select distinct
            txn.user_id,
            extract(year from txn.purchase_date) as purch_yr,
            CASE 
                WHEN EXTRACT(QUARTER FROM txn.purchase_date) IN (1, 2) THEN 1 
                WHEN EXTRACT(QUARTER FROM txn.purchase_date) IN (3, 4) THEN 2
            END AS purch_period,
    
        from trans txn
        group by user_id, purch_yr, purch_period
    ) as res
)

tb2 as (
    select 
        *,
        sum(*)   -- intend to get sum of each columns
    from trans
    group by user_id, purch_yr, purch_period
)
select tb1.*, tb2.*
from tb1 left join tb2 on tb2.user_id=tb1.user_id

I intend to sum of each columns using sum(*) and group them by user_id, purch_yr, purch_period but above attempt didn't fetch any data though. The reason of doing this because I have so many columns and I need to do sum(col1), ..., sum(coln). Above attempt didn't work.

update- new attempt:

this is my another attempt but I got null values as sum of columns values:

select res.*
from (
  select distinct
    purchase_dateuser_id,
    extract(year from purchase_datepurchase_date) as purch_yr,
    CASE
        WHEN EXTRACT(QUARTER FROM purchase_datepurchase_date) IN (1, 2) THEN 1 
        WHEN EXTRACT(QUARTER FROM purchase_datepurchase_date) IN (3, 4) THEN 2
    END AS purch_period,
    SUM(prod_ab_amt),
    SUM(prod_bf_amt),
    SUM(prod_gm_amt),
    SUM(catg_cg_amt),
    SUM(catg_dh_amt),
    SUM(catg_xy_amt),
    SUM(catg_mk_amt),
    SUM(brand_hk_amt),
    SUM(brand_hp_amt),
    SUM(brand_vm_amt)
  from trans
  group by user_id, purch_yr, purch_period
) as res

in this attempt, I end up with null values as sum of columns values, any better ideas on this?

sql server solution from SO

I found this solution but it is specific for sql server though:

select ',SUM(' + QUOTENAME(name) + ') AS ' + QUOTENAME(name)
from sys.columns sc
WHERE sc.object_id = OBJECT_ID('trans')
AND sc.name NOT IN ('user_id', 'purchase_date')

Can anyone suggest possible workaround on this? How can I do this either in big query standard sql or similar one?

output:

here is my final output that I want to produce in this dbfiddle:

Is there any way I can get my expected output table? any suggestion on above big query sql?

Answer 1

Change your table so each type is in its own row, instead of distinct columns.

CREATE TABLE trans (
    user_id varchar(10),
    purchase_date DATE,
    amt_type VARCHAR(10), -- prod_ab, prod_bf, catg_c, etc.
    amt FLOAT
);

Then you can use a query like:

SELECT user_id, purchase_date, amt_type, SUM(amt) AS total_amt
FROM trans
GROUP BY user_id, purchase_date, amt_type

If you want to get all the sums in a single row for a user+date, you can use PIVOT. See How to Pivot table in BigQuery