CODEWITHSUNDEEP
sqlvariablesjoin
Eda
Eda

Reputation: 208

Store the result of a JOIN query in a variable

Looks like something is off with my answer to the following question: A.How many Sales Orders (Headers) used ColonialView credit cards between June 2004 and (end of)June 2006. B.Store the output in a variable.

The output for the SELECT @SalesOrders is 1 . Should it be 1 or should it be the same as the number of Colonial Voice cards used (which is 729)?

This is my code

SELECT  b.CardType, COUNT(*) usdCV
FROM    Sales-SalesOrderHeader a 
JOIN    Sales-CreditCard b
ON      a-CreditCardID=b-CreditCardID
WHERE   b-CardType = 'ColonialVoice'
AND     a-OrderDate  between '06/1/2004' and '06/30/2006'
GROUP BY b.CardType

DECLARE @SalesOrders INT
SET @SalesOrders = COUNT (*)
SELECT @SalesOrders

Upvotes: 0

Views: 2524

Answers (1)

Rahul
Rahul

Reputation: 77906

You should try doing it like

DECLARE @SalesOrders INT;

SELECT @SalesOrders = COUNT(*) FROM Sales.SalesOrderHeader a 
JOIN Sales.CreditCard b 
ON a.CreditCardID=b.CreditCardID 
WHERE b.CardType = 'ColonialVoice' 
AND a.OrderDate between '06/1/2004' and '06/30/2006';

Upvotes: 1

Related Questions