Grouping output with SQL across multiple fields

Question

Starting with a simple table that contains

Date,Result,Account

where there can be more than one entry for a date&account combination and where there are some dates without entries for all accounts.

I'd like to output:

Date, Account1 sum(Result), Account2 sum(Result), etc...

In cases where there is data for one Account but not another Account for a certain date, I want to ensure there is still an entry for that date. Currently my query does the above but only creates an entry when there is a value stored for all Accounts selected.

Select a.Date
,isnull(Sum(a.Result),0) as Total_Account1
,isnull(Sum(b.Result),0) as Total_Account2
From MyTable a
join MyTable b on a.Date = b.Date
Where a.Account = 'Account1'
and b.Account = 'Account2'
Group by a.Date,b.Date
Order By a.Date

why don't union both tables so you get all the dates and then group by . Use case statement to sum by account. — Pals
– Pals, Commented Jun 11, 2020 at 21:56

GMB · Accepted Answer · 2020-06-11 22:05:01Z

1

I would recommend conditional aggregation rather than a self-join. It properly handles the dates when not both accounts are available, and will be more efficient since you don't need to scan the table twice:

select
    date,
    sum(case when account = 'Account1' then result else 0 end) total_account_1,
    sum(case when account = 'Account2' then result else 0 end) total_account_2
from mytable
where account in ('Account1', 'Account2')  -- this might not be necessary
group by date

The above query uses a SQL syntax that is supported by most databases, with case expression in the aggregate functions. Depending on your actual database (which you did not disclose), neater options may be available.

answered Jun 11, 2020 at 22:05

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Grouping output with SQL across multiple fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related