How to add multiple where clause? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to add multiple where clause?

Author

Topic

lavenzo
Starting Member

7 Posts

Posted - 2013-05-03 : 03:41:37

Hi, how can I eliminate the sub query as below? For exmaple:

SELECT PRODUCT_NAME,
(SELECT COUNT(REF_NO) FROM TABLE_A WHERE STATUS = 'PASS' AND CREATE_DATE = TODAY()) AS COUNT_PASS,
(SELECT COUNT(REF_NO) FROM TABLE_A WHERE STATUS = 'FAIL' AND CREATE_DATE = TODAY()) AS COUNT_FAIL
FROM TABLE_A
WHERE CREATE_DATE = TODAY()

As you can see I want 2 different count of number base on status but from same table, how can make it 1 query instead so many sub query?

lavenzo
Starting Member

7 Posts

Posted - 2013-05-03 : 03:44:28

can i make something like:

SELECT PRODUCT_NAME,
(COUNT(REF_NO) FROM TABLE_A WHERE STATUS = 'PASS') AS COUNT_PASS,
(COUNT(REF_NO) FROM TABLE_A WHERE STATUS = 'FAIL') AS COUNT_FAIL
FROM TABLE_A
WHERE CREATE_DATE = TODAY()?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-05-03 : 04:05:22

try this, but we are here on ms sql server only and TODAY() is mysql?
select
product_name,
count(case when status='PASS' then REF_NO end) as cnt_pass,
count(case when status='FAIL' then REF_NO end) as cnt_fail
from table_a
where create_date = today()

Too old to Rock'n'Roll too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-03 : 04:12:05

quote:
Originally posted by webfred

try this, but we are here on ms sql server only and TODAY() is mysql?
select
product_name,
count(case when status='PASS' then REF_NO end) as cnt_pass,
count(case when status='FAIL' then REF_NO end) as cnt_fail
from table_a
where create_date = today()
GROUP BY product_name
Too old to Rock'n'Roll too young to die.

in any case it would need a GROUP BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-05-03 : 04:13:01

quote:
Originally posted by visakh16

quote:
Originally posted by webfred

try this, but we are here on ms sql server only and TODAY() is mysql?
select
product_name,
count(case when status='PASS' then REF_NO end) as cnt_pass,
count(case when status='FAIL' then REF_NO end) as cnt_fail
from table_a
where create_date = today()
GROUP BY product_name
Too old to Rock'n'Roll too young to die.

in any case it would need a GROUP BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Oops - yes

thx

Too old to Rock'n'Roll too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-03 : 04:14:04

also create_date = today() wont work as intended if create_date field has timepart. so you may be better off writing logic like

create_date >= dateadd(dd,datediff(dd,0, today()),0)
and create_date < dateadd(dd,datediff(dd,0, today()),1)

Ofcourse all these suggestions are based on SQL Server assumptions so if it doesnt work in MySQL try to get specific help from MySQL forums like www.dbforums.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-05-03 : 19:14:11

today() is not MySQL. It's probably Excel.

This is just to be weird.

You can write the original query in a fashion that will work.

Note, I'm leaving the dates alone, and broken. This is just conceptual.

SELECT a.PRODUCT_NAME,
(SELECT COUNT(REF_NO) FROM TABLE_A WHERE STATUS = 'PASS' AND CREATE_DATE = TODAY() AND a.PRODUCT_NAME = PRODUCT_NAME) AS COUNT_PASS,
(SELECT COUNT(REF_NO) FROM TABLE_A WHERE STATUS = 'FAIL' AND CREATE_DATE = TODAY() AND a.PRODUCT_NAME = PRODUCT_NAME) AS COUNT_FAIL
FROM table_a a

This will work if there are no duplicate records for product name. If PRODUCT_NAME shows up more than once then adding a DISTINCT to the beginning of the query would work.

There is no earthly reason to do this, at least not in this example, but you can do it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources