By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Where clause

P: n/a
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
Oct 27 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Oct 27 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.