How to write a SQL query implementing Excel SUMIFS function
23,809
Your query is ok, but it can be improved a bit:
SELEC Date,
MAX(CASE WHEN Department = 'Toys' THEN Sales else 0 END) as [Toys],
MAX(CASE WHEN Department = 'Movies' THEN Sales else 0 END) as [Movies]
FROM Table$
WHERE store in ('A', 'B')
GROUP BY Date
ORDER BY Date;
This removes the distinct
, which is unnecessary with a group by
. It moves the condition on store
to the where
clause, because it applies to all the rows. And, it removes the ISNULL()
by including else 0
in the case
statement -- so stores with no sales in the department will return a 0
instead of NULL
.
Author by
Lorenzo Rigamonti
Updated on July 20, 2022Comments
-
Lorenzo Rigamonti almost 2 years
I have a table like this:
Date Sales Department Store 02/01/2012 4.09 Toys A 03/01/2012 5.90 Toys A 02/01/2012 6.64 Toys B 03/01/2012 4.71 Toys B 02/01/2012 16.72 Toys C 03/01/2012 1.37 Toys C 02/01/2012 13.22 Movies A 03/01/2012 18.06 Movies A 02/01/2012 5.97 Movies B 03/01/2012 16.71 Movies B 02/01/2012 15.38 Movies C 03/01/2012 19.75 Movies C
And want a table like this, where only Store A and B are considered:
Date Toys Movies 02/01/2012 10.73 19.19 03/01/2012 10.61 34.77
Here the SUMIFS function we would use in EXCEL:
=SUMIFS(Value;Date;$H4;Department;I$3;Store;"<>C")
What can we write in SQL?
Take into consideration this is a short exaple, the database table has over 30 Departments and more many Dates. I was using the script
SELECT DISTINCT Date, ISNULL(MAX(CASE WHEN Department = 'Toys' AND Store = 'A' THEN Sales END),0) + ISNULL(MAX(CASE WHEN Department = 'Toys' AND Store = 'B' THEN Sales END),0) [Toys], ISNULL(MAX(CASE WHEN Department = 'Movies' AND Store = 'A' THEN Sales END),0) + ISNULL(MAX(CASE WHEN Department = 'Movies' AND Store = 'B' THEN Sales END),0) [Movies] FROM Table$ GROUP BY Date ORDER BY Date
... but it is not efficient. Thanks for any tips.