CODEWITHSUNDEEP
sql-serverdatabase
Trevor Hart
Trevor Hart

Reputation: 1023

SQL: Summing columns with a similar column in common

I'm extremely new to SQL Sever and so I apologize if the question is worded strange. I am doing a homework assignment, and this is the question:

"A manager wants to know the email address, number or orders, and the total amount of purchases made by each customer. Create a summary query that returns these three items for each customer that has orders."

I have all of the data queried, the problem is when I pull data from each customer, it will show the quantity of items per order, and I need the items to be pooled together into one column. This is my query thus far (again, total noob, please excuse any poor syntax, etc.)

SELECT EmailAddress,
ItemPrice - DiscountAmount * Quantity AS TotalPurchaseAmount,
COUNT(*) AS OrderQty
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
GROUP BY Orders.CustomerID, 
OrderItems.ItemPrice, OrderItems.DiscountAmount,
OrderItems.Quantity,
Customers.EmailAddress;

The following is a small bit of the result set that I get:

Email Address               OrderTotal  OrderQty
[email protected]    253.15      2
[email protected]    839.30      2
[email protected]    1208.16     2
[email protected]            303.79      4
[email protected]     479.60      2
[email protected] 299.00      2
[email protected] 489.30      1
[email protected] 479.60      1

So as you can see, I have several orders I need to smoosh together into one single row per e-mail, I have looked and looked for an answer but the only thing I can find is how to find duplicates and ignore them, not combine their data. Any help is extremely appreciate, thanks so much for taking the time to read this :) If my question doesn't make sense please let me know so I can clear up any bad wording I may have used!

Upvotes: 0

Views: 75

Answers (2)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

You need to change your formula and remove columns that you dont want to group by from select query.. for example your query should be something like this

SELECT EmailAddress,
--do your aggregation here
blah AS TotalPurchaseAmount,
COUNT(*) AS OrderQty
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
GROUP BY Orders.CustomerID, 
Customers.EmailAddress;

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

Just do GROUP BY CustomerID, EmailAddress:

SELECT
    c.EmailAddress,
    SUM((i.ItemPrice - i.DiscountAmount) * Quantity) AS TotalPurchaseAmount,
    COUNT(*) AS OrderQty
FROM Customers c
INNER JOIN Orders o
    ON c.CustomerID = o.CustomerID
INNER JOIN OrderItems i
    ON o.OrderID = i.OrderID
GROUP BY
    c.CustomerID, c.EmailAddress

Additional note: Use aliases for your tables

Upvotes: 1

Related Questions