CODEWITHSUNDEEP
sqldategroup-bysumpivot
idontknow
idontknow

Reputation: 3

Grouping output with SQL across multiple fields

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

Upvotes: 0

Views: 32

Answers (1)

GMB
GMB

Reputation: 222482

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.

Upvotes: 1

Related Questions