467,915 Members | 1,270 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

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
Oct 27 '08 #1
  • viewed: 1053
Share:
1 Reply
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.

Similar topics

47 posts views Thread by Andrey Tatarinov | last post: by
2 posts views Thread by steve | last post: by
3 posts views Thread by A.V.C. | last post: by
2 posts views Thread by Jim.Mueksch | last post: by
5 posts views Thread by pwiegers | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.