How can I run multiple select statements getting individual counts for each select in php mysql-CodePudding

I am trying to run a select where I count the records from six seperate SELECTS as individual counts, not a total count. How can I run these as one statement?

SELECT COUNT(*) as firstweekcount FROM booking WHERE ((start_date)<='$monday_week1' AND date(end_date)>='$sunday_week1') GROUP BY screen_id

SELECT COUNT(*) as secondweekcount FROM booking WHERE ((start_date)<='$monday_week2' AND date(end_date)>='$sunday_week2') GROUP BY screen_id

SELECT COUNT(*) as thirdweekcount FROM booking WHERE ((start_date)<='$monday_week3' AND date(end_date)>='$sunday_week3') GROUP BY screen_id

...and so on through to number six.

I did attempt to use the below statement but it is giving me the wrong record counts. Any help would be gladly appreciated. Thank you

SELECT screen, screen_code, screen_id,
                    SUM((start_date)<='$monday_week1' AND date(end_date)>='$sunday_week1') as firstweekcount,
                    SUM((start_date)<='$monday_week2' AND date(end_date)>='$sunday_week2') as secondweekcount,
                    SUM((start_date)<='$monday_week3' AND date(end_date)>='$sunday_week3') as thirdweekcount,
                    SUM((start_date)<='$monday_week4' AND date(end_date)>='$sunday_week4') as fourthweekcount,
                    SUM((start_date)<='$monday_week5' AND date(end_date)>='$sunday_week5') as fifthweekcount,
                    SUM((start_date)<='$monday_week6' AND date(end_date)>='$sunday_week6') as sixthweekcount
                    FROM booking GROUP BY screen_id

CodePudding user response：

Easy slow way:

SELECT (*query1*), (*query2*), (*query3*) FROM dual;

in your case:

SELECT (SELECT COUNT(*) as firstweekcount FROM booking WHERE ((start_date)<='$monday_week1' AND date(end_date)>='$sunday_week1') GROUP BY screen_id
), (SELECT COUNT(*) as secondweekcount FROM booking WHERE ((start_date)<='$monday_week2' AND date(end_date)>='$sunday_week2') GROUP BY screen_id
), (SELECT COUNT(*) as thirdweekcount FROM booking WHERE ((start_date)<='$monday_week3' AND date(end_date)>='$sunday_week3') GROUP BY screen_id
) FROM dual;

Performance way:

use a CASE statement with an aggregate function

CodePudding user response：

Slightly faster way:

The expression within SUM is a TRUE (1) or FALSE (0) expression so SUMming them up is the count you are after.

SELECT
  SUM( start_date <='$monday_week1' AND date(end_date)>='$sunday_week1') as firstweekcount,
  SUM(start_date <='$monday_week2' AND date(end_date)>='$sunday_week2')  as secondweekcount,
  SUM(start_date<='$monday_week3' AND date(end_date)>='$sunday_week3') as thirdweekcount
FROM booking
GROUP BY screen_id