CODEWITHSUNDEEP
sqlsql-server
Bill Gates
Bill Gates

Reputation: 857

SQL Server Grouping issue

I have two tables.

  1. Customer ID, NAME, MonthlyIncome
  2. Orders ID, CustomerKey, TotalCost

I want join those two table into one, in the way that having total amount the bought in the shop.

CustomerKey, MonthlyIncome, TotalCost.

I have tried to group by CustomerKey, but I am not able to include the rest of the columns. Which query should I be using?

SELECT 
    fs.CustomerKey, SUM(fs.TotalCost) as TotalBought
FROM 
    FactOnlineSales fs
INNER JOIN 
    DimCustomer c on c.CustomerKey = fs.CustomerKey
GROUP BY 
    fs.CustomerKey

Upvotes: 0

Views: 67

Answers (3)

Andrea Colleoni
Andrea Colleoni

Reputation: 6021

You may have an error in the ON clause of the join.
You used CustomerKey instead of Customer ID from the DimCustomer table. For gouping: you must group by every column you don't aggregate on, so you can include NAME (so you can have descriptive customer) and MonthlyIncome.

SELECT 
    c."Customer ID",
    c.NAME, 
    c.MonthlyIncome,
    SUM(fs.TotalCost) as TotalBought
FROM 
    FactOnlineSales fs INNER JOIN DimCustomer c 
    on c."Customer ID" = fs.CustomerKey
GROUP BY 
    c."Customer ID",
    c.NAME,
    c.MonthlyIncome

Upvotes: 1

marc_s
marc_s

Reputation: 754398

You didn't mention what version of SQL Server you're using - but if you're on SQL Server 2005 or newer, you could use the OVER() clause:

SELECT 
    fs.CustomerKey, 
    c.MonthlyIncome,
    TotalBought = SUM(fs.TotalCost) OVER(PARTITION BY fs.CustomerKey)
FROM 
    FactOnlineSales fs
INNER JOIN 
    DimCustomer c on c.CustomerKey = fs.CustomerKey

That basically gives you the CustomerKey, the MonthlyIncome and then sums the TotalCost for each CustomerKey and displays the appropriate value in your output. No need to do a GROUP BY in this case

Upvotes: 1

ta.speot.is
ta.speot.is

Reputation: 27214

I have tried to group by CustomerKey, but not able to manage how to include rest of the columns.

Just do it:

SELECT fs.CustomerKey,
       c.MonthlyIncome,
       SUM(fs.TotalCost) as TotalBought
  FROM FactOnlineSales fs
       INNER JOIN DimCustomer c
         ON c.CustomerKey = fs.CustomerKey
 GROUP BY fs.CustomerKey,
          c.MonthlyIncome

Presumably CustomerKey is unique, so grouping by a less unique column isn't going to change the SUM total.

Upvotes: 1

Related Questions