Where clause

Having problems adding this where clause (where
week_start_date='20081003') I have two dates in the database but I
want the count from only 20081003.

SELECT PAN,
SUM(CASE WHEN Cat=1 THEN 1 ELSE 0 END) AS Drycd1,
SUM(CASE WHEN Cat=2 THEN 1 ELSE 0 END) AS Drycd2,
SUM(CASE WHEN Cat=3 THEN 1 ELSE 0 END) AS Drycd3,
SUM(CASE WHEN Cat=4 THEN 1 ELSE 0 END) AS Drycd4,
SUM(CASE WHEN Cat=5 THEN 1 ELSE 0 END) AS Drycd5,
SUM(CASE WHEN Cat=6 THEN 1 ELSE 0 END) AS Drycd6,
SUM(CASE WHEN Cat=7 THEN 1 ELSE 0 END) AS Drycd7,
COUNT(*) AS CountTotal
FROM

(
SELECT PAN,1 AS cat FROM DryCD1
UNION ALL
SELECT PAN,2 FROM DryCD2
UNION ALL
SELECT PAN,3 FROM DryCD3
UNION ALL
SELECT PAN,4 FROM DryCD4
UNION ALL
SELECT PAN,5 FROM DryCD5
UNION ALL
SELECT PAN,6 FROM DryCD6
UNION ALL
SELECT PAN,7 FROM DryCD7
)t
GROUP BY PAN

re: Where clause

Assuming the column is in the tables you union, then you can add the
WHERE filter to each table, like:

SELECT PAN,1 AS cat FROM DryCD1
WHERE week_start_date='20081003'
UNION ALL ...


Or, you can add it as column to the derived table (you have to add it to
all SELECT statements) and then filter later:

....
FROM (
SELECT PAN,1 AS cat, week_start_date FROM DryCD1
UNION ALL
SELECT PAN,2, week_start_date FROM DryCD2
....) AS T
WHERE week_start_date = '20081003'

--
Plamen Ratchev
http://www.SQLStudio.com