These IDs seem like they should beNitpicks
SQL code (like pretty much all code) reads a table aslot easier when using line breaks and indentation.
s is not a very good alias. You want your aliases to say something about what it means, there are several queries that usenot just shorten the same list of IDscode. The following needs to be refactoredIn this case I would not even use one, I feel Sales is plenty short.
Your code so far:
SELECT SUM(
CASE WHEN sSales.TypeID IN(1,2,3,4,7,8,10) THEN 1
ELSE 0
END
)
FROM Sales sSales;
The problemCommon Table Expression
You could organize the code a little better by using a CTE, although it is a subquery can't be used in an aggregate function. The following causes an errorbit more verbose:
WITH SalesInUS AS(
SELECT ID
FROM SalesType
WHERE SalesType.Desc = 'US'
),
SELECT SUM(
CASE WHEN sSales.TypeID IN( SalesInUS THEN 1
ELSE 0
END
)
FROM Sales;
But if this is often referenced, you are correct: a table would work better.
Example:
INSERT INTO #SalesInUS
SELECT ID
FROM SaleTypesSalesType
WHERE SaleTypeSalesType.Desc = 'US'
Then you just join that table when you need it. Note I changed SUM to COUNT as well.
SELECT COUNT(Sales.TypeID)
FROM THENSales 1
INNER ELSEJOIN 0#SalesInUS
ON END)Sales.TypeID = #SalesInUS.ID
Alternatively, you could also use a simple JOIN
SELECT COUNT(Sales.TypeID)
FROM Sales s
INNER JOIN SalesType
ON Sales.TypeID = SalesType.ID
AND SalesType.Desc = 'US'
