Using sum function
-
Hi, I need to sum up a customers total purchases, the trick is I need to display customers who have not purchased a certain product so use the where clause, this then sums up the products selected and leaves out some
SELECT Customer_Number, SUM(Price) As TOTAL FROM CUSTOMER_PURCAHSES WHERE Product_ID IN(223) AND Product_ID NOT IN(224, 225) GROUP BY Customer_NumberThis returns Customer_Number 23 with a total of 90, but he has another product purchase, 227, how do I get my query to add that Product_ID to the sum only?
-
I'm not 100% sure what you want as you've not really posted enough information
I've had a guess anyway is this what you are after?
SELECT
Customer_Number
,SUM(Price) As TOTAL
FROM
CUSTOMER_PURCAHSES
WHERE
Product_ID IN(223,227)
AND Product_ID NOT IN(224, 225)
GROUP BY
Customer_Number
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe -
soulchyld21 (2/9/2012)
Hi, I need to sum up a customers total purchases, the trick is I need to display customers who have not purchased a certain product so use the where clause, this then sums up the products selected and leaves out someSELECT Customer_Number, SUM(Price) As TOTAL FROM CUSTOMER_PURCAHSES WHERE Product_ID IN(223) AND Product_ID NOT IN(224, 225) GROUP BY Customer_NumberThis returns Customer_Number 23 with a total of 90, but he has another product purchase, 227, how do I get my query to add that Product_ID to the sum only?
tHIS MAY WORK FOR YOU.
SELECT Customer_Number,Product_ID , SUM(Price) As TOTAL FROM CUSTOMER_PURCAHSES WHERE Product_ID IN(223,227) GROUP BY Customer_Number,Product_ID -
That was the first thing I thought of, problem with that is it also gives me the customers who have not bought the products in the IN() part of the where clause!
I have even tried
SELECT Customer_Number, SUM (Price) As TOTAL FROM Customer_PurchasesWHERE Product_Number IN(SELECT Product_Number)
AND Product_Number IN ('1','2')
AND Product_Number NOT IN('3')
Also doesn't work, it limits the total SUM() to prod 1 and 2
-
ignore the syntax above, I did include the FROM in the subquery
-
Still not 100% sure what your after but if I have understood correctly you only want to SUM the customers who have purchased products 223,227 and no others if so this will achieve it - depending on the size of your data there are more efficient ways of achieving the same result:
SELECT
Customer_Number
,SUM(Price) As TOTAL
FROM
CUSTOMER_PURCAHSES
WHERE
Product_ID IN(223,227)
AND Customer_Number NOT IN
(SELECT
Customer_Number
FROM
CUSTOMER_PURCAHSES
WHERE
Product_ID NOT IN (223,227)
)
GROUP BY
Customer_Number
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe -
I am trying to say give me all customer numbers and their total purchases SUM() for those customers who have bought product b and any other products besides c and d, so if they have bought a, id like to have that data too!
-
This will SUM all products for the customers who have not bought C and D - Hope it helps
SELECT
Customer_Number
,SUM(Price) As TOTAL
FROM
CUSTOMER_PURCAHSES
WHERE
Customer_Number NOT IN
(SELECT
Customer_Number
FROM
CUSTOMER_PURCAHSES
WHERE
Product_ID IN ('C','D') --Replace with the associated ID's
)
GROUP BY
Customer_Number
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe -
See if this helps
SELECT Customer_Number,SUM(Price) As TOTAL,
COUNT(CASE WHEN Product_ID IN (223) THEN Product_ID END) AS CountOf223Bought,
COUNT(CASE WHEN Product_ID IN(224,225) THEN Product_ID END) AS CountOf224or225Bought
FROM CUSTOMER_PURCAHSES
GROUP BY Customer_Number
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
@AndyHyslop Thanks that is almost what I am after, customers who have not bought products b and c however I only want results from these customers (who haven't bought b and c) who have bought product a, say products b and c compliment product a and Id like to sell these people products b or c, however I'd also like to be able to see how much the customers I am targeting have spent, even on say product z, sorry I should have explicitly stated what I am trying to achieve overall!:-)
-
So if I understand correctly:
SELECT
Customer_Number
,Product_ID
,SUM(Price) As TOTAL
FROM
CUSTOMER_PURCAHSES
WHERE
Product_ID = 'A'
AND Customer_Number NOT IN
(SELECT
Customer_Number
FROM
CUSTOMER_PURCAHSES
WHERE
Product_ID IN ('C','D') --Replace with the associated ID's
)
GROUP BY
Customer_Number
,Product_ID
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe -
Actually think I may have misunderstood this should work better, its not very pretty but it gets the job done I suppose:
-- Build Test Data
DECLARE @PRODA AS TABLE
(
Customer_Number int
,Product_ID CHAR (1)
,Price MONEY
)
INSERT INTO @PRODA
VALUES(1,'A',100),
(1,'B',100),
(1,'A',100),
(1,'B',100),
(2,'B',100),
(3,'C',100),
(4,'D',100)
;
-- Create CTE for All Products
WITH ALLPRODS
AS
(
SELECT
Customer_Number
,Product_ID --Uncomment if you want to see the product breakdown
,SUM(Price) As ProductTotal
FROM
@PRODA
GROUP BY
Customer_Number
,Product_ID
),
-- Create CTE for Product A WHERE not in C or D
PRODA
AS
(
SELECT
Customer_Number
,Product_ID
,SUM(Price) As TotalANotinCandD
FROM
@PRODA
WHERE
Product_ID = 'A'
AND Customer_Number NOT IN
(SELECT
Customer_Number
FROM
@PRODA
WHERE
Product_ID IN ('C','D') --Replace with the associated ID's
)
GROUP BY
Customer_Number
,Product_ID
)
--Join the two together to get results
SELECT
*
FROM ALLPRODS
LEFT JOIN PRODA ON ALLPRODS.Customer_Number = PRODA.Customer_Number AND ALLPRODS.Product_ID = PRODA.Product_ID
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply