Question

Locked

Is is possible to have multiple WHERE clauses in SELECT?

By tthomas ·
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

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Use AND

by scott_heath In reply to Is is possible to have mu ...

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.

Collapse -

CASE Statement Took Care of It.

by tthomas In reply to Use AND

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

Collapse -

Sorry!

by scott_heath In reply to CASE Statement Took Care ...

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

Collapse -

Learning is always a good thing!

by tthomas In reply to Sorry!

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! ) This little line of code did me wonders!

Thanks for your quick response!

Collapse -

Can't have multiple WHERE Clauses

by tthomas In reply to Is is possible to have mu ...

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

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums