Im just learning SQL mostly by myself, and Im doing a practice Project from ‘preppindata’ (its made for Tableau Prep but they say you can used it to practice SQL too)
So I have a table with the columns Bank, Customer_Code and Value.
I am asked get the Total Values by Bank and Customer Code.
SELECT
SUM(CASE WHEN Bank = 'DS' AND Customer_Code = 100001 THEN Value ELSE 0 END) AS DS_100001,
SUM(CASE WHEN Bank = 'DS' AND Customer_Code = 100002 THEN Value ELSE 0 END) AS DS_100002,
SUM(CASE WHEN Bank = 'DS' AND Customer_Code = 100003 THEN Value ELSE 0 END) AS DS_100003,
SUM(CASE WHEN Bank = 'DS' AND Customer_Code = 100004 THEN Value ELSE 0 END) AS DS_100004,
SUM(CASE WHEN Bank = 'DS' AND Customer_Code = 100005 THEN Value ELSE 0 END) AS DS_100005,
SUM(CASE WHEN Bank = 'DS' AND Customer_Code = 100006 THEN Value ELSE 0 END) AS DS_100006,
SUM(CASE WHEN Bank = 'DS' AND Customer_Code = 100007 THEN Value ELSE 0 END) AS DS_100007,
SUM(CASE WHEN Bank = 'DS' AND Customer_Code = 100008 THEN Value ELSE 0 END) AS DS_100008,
SUM(CASE WHEN Bank = 'DS' AND Customer_Code = 100009 THEN Value ELSE 0 END) AS DS_100009,
SUM(CASE WHEN Bank = 'DS' AND Customer_Code = 100010 THEN Value ELSE 0 END) AS DS_1000010
FROM Data_Source_Bank
So far I did this, I would have to rinse and repeat with the next 2 Banks. It works and gives me the values I need. But My question is:
IS there a more efficient, short and logical way to do this in SQL?
(Im using SQL Server btw)
>Solution :
The simplest method is to use the GROUP BY clause, which will return a row for each combination of Bank and ‘Customer Code’.
SELECT Bank, Customer_Code, SUM(value)
FROM Data_Source_Bank
GROUP BY Bank, Customer_Code