SQL Server SUM Values by Different Customer Codes and Banks at the same time

emeijp43  于 2023-05-21  发布在  其他
关注(0)|答案(1)|浏览(124)

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)

toiithl6

toiithl61#

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

相关问题