Question Locked

Is is possible to have multiple WHERE clauses in SELECT?

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

If I have a table called "content_hits_tbl" and want to pull information, can't i write something like this:

===============
SELECT COUNT(visitor_id) AS HITS, COUNT(DISTINCT visitor_id) AS VISITORS, COUNT(DISTINCT visitor_id) WHERE visit_type = 0 AS NEW, COUNT(DISTINCT visitor_id) WHERE visit_type = 1 AS RETURNING
FROM content_hits_tbl
===============

Can't you have multiple WHERE clauses in the SELECT statement?

Any suggestions would be great. I have been wrestling with it and SQL queries arent my strong area ...

I'm using VS05 connecting to a SQL database.

Sincerely,
Tommy

6 replies

by scott_heath

Like this:

===============
SELECT COUNT(visitor_id)
AS HITS, COUNT(DISTINCT visitor_id)
AS VISITORS, COUNT(DISTINCT visitor_id)
WHERE visit_type = 0
AS NEW, COUNT(DISTINCT visitor_id)
AND visit_type = 1
AS RETURNING
FROM content_hits_tbl
===============

I like to start a new line for keywords. ;)

by tthomas

Using AND works in the WHERE statement, but I needed to use the CASE statement to pull a WHERE clause on the 2 different individual columns and not on all the columns of data being pulled.

The CASE statements worked. I appreciate your time!

Tommy

by scott_heath

I'm still fairly new to SQL myself! Of ocurse I learned something, which is always good!

by tthomas

A friend gave me the answer:

You cannot have multiple where clauses as you are using them, but you are on the right track. You need to use a Case statement to get the conditional.

Select count(visitor_id) as total_visits,
count(distinct visitor_id) as total_visitors,
count(Distinct Case when visit_type = 0 then visitor_id end ) as new_visitors,
count(Distinct Case when visit_type = 1 then visitor_id end ) as returning
from content_hits_tbl

by tthomas

No problem! I have been learning a lot of stuff lately. Here's a good one to know:
===========
WHERE (datadate BETWEEN DATEADD(mm, - 3, GETDATE()) AND GETDATE())
===========
Pulls the records for the last 3 months from the day you you pull the data which would be, more or less, today. "datadate" would be a column in your table that is a date field obviously! :o) This little line of code did me wonders!

Thanks for your quick response!

This discussion is closed to new replies.