SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to add multiple where clause?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lavenzo
Starting Member

7 Posts

Posted - 05/03/2013 :  03:41:37  Show Profile  Reply with Quote
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 - 05/03/2013 :  03:44:28  Show Profile  Reply with Quote
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()?
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 05/03/2013 :  04:05:22  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/03/2013 :  04:12:05  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 05/03/2013 :  04:13:01  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/03/2013 :  04:14:04  Show Profile  Reply with Quote
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
Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 05/03/2013 :  19:14:11  Show Profile  Reply with Quote
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.



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000