April 5, 2012 at 10:25 am
I just started learning SQL Server. From three tables Customers, Sales and Product; I want a query that returns the total sales of each customer. Here's my tables and a few queries that I have written. Any help will be greatly appreciated.
CREATE TABLE Customers
(
CustomerName varchar(50),
City varchar(50),
State varchar(20)
);
CREATE TABLE Products
(
ProductName varchar(50),
Descr varchar(50),
Price money
);
CREATE TABLE Sales
(
SaleDate datetime,
ProductName varchar(50),
CustomerName varchar(50),
Quantity int
);
-- This query gives total sales of Each Product
SELECT Sales.ProductName, SUM(Sales.Quantity) AS Total, Products.Price,
'Total Sale' = (SUM(Sales.Quantity) * Products.Price)
FROM Products
INNER JOIN Sales ON Products.ProductName = Sales.ProductName
GROUP BY Sales.ProductName, Products.Price
ORDER BY 'Total Sale' DESC
-- Need a query that gives total sales of each customer
-- How do I sum the count of sales for each customer? This is what I have thus far
SELECT C.CustomerName, S.Quantity, P.Price
FROM Customers AS C
INNER JOIN Sales AS S
ON C.CustomerName = S.CustomerName
INNER JOIN Products As P
ON S.ProductName = P.ProductName
GROUP BY C.CustomerName, S.Quantity, P.Price
April 5, 2012 at 10:31 am
You would want a CTE to sum up quantuty and price when grouped by customer, product. Then sum up the price grouped by customer.
Jared
CE - Microsoft
April 5, 2012 at 11:54 am
Can you populate the tables with some data? And to go with it, your expected result based off of that sample data!
April 5, 2012 at 12:27 pm
Here you go.
USE Master
GO
CREATE DATABASE JoinTest;
GO
USE JoinTest;
GO
CREATE TABLE Customers
(
CustomerName varchar(50),
Addr varchar(50),
City varchar(50)
);
CREATE TABLE Products
(
ProductName varchar(50),
Descr varchar(50),
Price money
);
CREATE TABLE Sales
(
SaleDate datetime,
ProductName varchar(50),
CustomerName varchar(50),
Quantity int
);
GO
INSERT INTO Customers VALUES ('Ken', '123 Elm St', 'Milwuakee');
INSERT INTO Customers VALUES ('Laura', '456 South St', 'Chicago');
INSERT INTO Customers VALUES ('Mark', '789 Oak St', 'St Louis');
INSERT INTO Customers VALUES ('Steve', '21 Main St', 'Seattle');
INSERT INTO Customers VALUES ('Kendra', '123 Kent St', 'Denver');
INSERT INTO Products VALUES ('Green Widget', 'Large Green', 100.00);
INSERT INTO Products VALUES ('Blue Widget', 'XLarge Blue', 130.00);
INSERT INTO Products VALUES ('Red Widget', 'Medium Red', 99.50);
INSERT INTO Products VALUES ('Yellow Widget', 'Medium Yellow', 75.00);
INSERT INTO Products VALUES ('Black Widget', 'Small Black', 50.00);
INSERT INTO Sales VALUES ('01/01/07', 'Green Widget', 'Mark', 1);
INSERT INTO Sales VALUES ('02/02/07', 'Green Widget', 'Steve', 2);
INSERT INTO Sales VALUES ('02/02/07', 'Blue Widget', 'Laura', 3);
INSERT INTO Sales VALUES ('01/01/07', 'Blue Widget', 'Kendra', 1);
INSERT INTO Sales VALUES ('10/01/08', 'Red Widget', 'Steve', 2);
INSERT INTO Sales VALUES ('11/04/08', 'Black Widget', 'Steve', 4);
INSERT INTO Sales VALUES ('04/07/08', 'Green Widget', 'Laura', 2);
-- Get Total Sales By Product
SELECT Sales.ProductName, SUM(Sales.Quantity) AS Total, Products.Price,
'Total Sale' = (SUM(Sales.Quantity) * Products.Price)
FROM Products
INNER JOIN Sales ON Products.ProductName = Sales.ProductName
GROUP BY Sales.ProductName, Products.Price
ORDER BY 'Total Sale' DESC
-- Get Total Sales By Customer
SELECT C.CustomerName, S.Quantity, P.Price
FROM Customers AS C
INNER JOIN Sales AS S
ON C.CustomerName = S.CustomerName
INNER JOIN Products As P
ON S.ProductName = P.ProductName
GROUP BY C.CustomerName, S.Quantity, P.Price
Result would be something like this...
Customer name | Total Quantity | Total Price
Kendra | 1 | 130.00
Laura | 5 | 230.00
Mark | 1 | 100.00
Steve | 8 | 249.50
April 5, 2012 at 12:33 pm
This?
SELECT C.CustomerName, Qty = SUM( S.Quantity ) ,Prc = SUM( P.Price)
FROM Customers AS C
INNER JOIN Sales AS S
ON C.CustomerName = S.CustomerName
INNER JOIN Products As P
ON S.ProductName = P.ProductName
GROUP BY C.CustomerName
April 5, 2012 at 12:37 pm
tobinare (4/5/2012)
Here you go.
USE Master
GO
CREATE DATABASE JoinTest;
GO
USE JoinTest;
GO
CREATE TABLE Customers
(
CustomerName varchar(50),
Addr varchar(50),
City varchar(50)
);
CREATE TABLE Products
(
ProductName varchar(50),
Descr varchar(50),
Price money
);
CREATE TABLE Sales
(
SaleDate datetime,
ProductName varchar(50),
CustomerName varchar(50),
Quantity int
);
GO
INSERT INTO Customers VALUES ('Ken', '123 Elm St', 'Milwuakee');
INSERT INTO Customers VALUES ('Laura', '456 South St', 'Chicago');
INSERT INTO Customers VALUES ('Mark', '789 Oak St', 'St Louis');
INSERT INTO Customers VALUES ('Steve', '21 Main St', 'Seattle');
INSERT INTO Customers VALUES ('Kendra', '123 Kent St', 'Denver');
INSERT INTO Products VALUES ('Green Widget', 'Large Green', 100.00);
INSERT INTO Products VALUES ('Blue Widget', 'XLarge Blue', 130.00);
INSERT INTO Products VALUES ('Red Widget', 'Medium Red', 99.50);
INSERT INTO Products VALUES ('Yellow Widget', 'Medium Yellow', 75.00);
INSERT INTO Products VALUES ('Black Widget', 'Small Black', 50.00);
INSERT INTO Sales VALUES ('01/01/07', 'Green Widget', 'Mark', 1);
INSERT INTO Sales VALUES ('02/02/07', 'Green Widget', 'Steve', 2);
INSERT INTO Sales VALUES ('02/02/07', 'Blue Widget', 'Laura', 3);
INSERT INTO Sales VALUES ('01/01/07', 'Blue Widget', 'Kendra', 1);
INSERT INTO Sales VALUES ('10/01/08', 'Red Widget', 'Steve', 2);
INSERT INTO Sales VALUES ('11/04/08', 'Black Widget', 'Steve', 4);
INSERT INTO Sales VALUES ('04/07/08', 'Green Widget', 'Laura', 2);
-- Get Total Sales By Product
SELECT Sales.ProductName, SUM(Sales.Quantity) AS Total, Products.Price,
'Total Sale' = (SUM(Sales.Quantity) * Products.Price)
FROM Products
INNER JOIN Sales ON Products.ProductName = Sales.ProductName
GROUP BY Sales.ProductName, Products.Price
ORDER BY 'Total Sale' DESC
-- Get Total Sales By Customer
SELECT C.CustomerName, S.Quantity, P.Price
FROM Customers AS C
INNER JOIN Sales AS S
ON C.CustomerName = S.CustomerName
INNER JOIN Products As P
ON S.ProductName = P.ProductName
GROUP BY C.CustomerName, S.Quantity, P.Price
Result would be something like this...
Customer name | Total Quantity | Total Price
Kendra | 1 | 130.00
Laura | 5 | 230.00
Mark | 1 | 100.00
Steve | 8 | 249.50
Try this:
SELECT S.CustomerName, SUM(S.Quantity) AS TotalQuantity, SUM(S.Quantity*P.Price) AS TotalPrice
FROM Sales S
INNER JOIN Products P
ON S.ProductName = P.ProductName
GROUP BY S.CustomerName
Jared
CE - Microsoft
April 5, 2012 at 3:39 pm
Thanks SSCommitted and Jared. Both methods are useful to my understanding of SQL Server and workable. Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply