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.
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_FAILFROM TABLE_AWHERE 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_FAILFROM TABLE_AWHERE 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?selectproduct_name,count(case when status='PASS' then REF_NO end) as cnt_pass,count(case when status='FAIL' then REF_NO end) as cnt_failfrom table_awhere 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?selectproduct_name,count(case when status='PASS' then REF_NO end) as cnt_pass,count(case when status='FAIL' then REF_NO end) as cnt_failfrom table_awhere 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 MVPhttp://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?selectproduct_name,count(case when status='PASS' then REF_NO end) as cnt_pass,count(case when status='FAIL' then REF_NO end) as cnt_failfrom table_awhere 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 MVPhttp://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 likecreate_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 MVPhttp://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_FAILFROM table_a aThis 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. |
|
|
|
|
|
|
|