CODEWITHSUNDEEP
mysqlsql
Beginner
Beginner

Reputation: 1740

combining 2 queries in one Mysql

First SQL query : 

SELECT COUNT(*) FROM (
    SELECT
        rec_business.personal_id,
        rec_business.id AS id,
        rec_personal.l_name AS last_name,
        rec_personal.f_name AS first_name,
        rec_personal.m_name AS middle_name,
        rec_business.bus_name AS bus_name,
        rec_business.reg_type AS reg_type,
        rec_business_type.name AS buss_type,
        rec_business.address AS address,
        rec_business.reg_date AS reg_date,
        rec_business.expire_date AS exp_date
    FROM
        rec_business
        INNER JOIN rec_personal ON rec_business.personal_id = rec_personal.id 
        INNER JOIN rec_business_type ON rec_business_type.id = rec_business.bus_nature
    WHERE rec_business.expire_date > NOW()
) t

Second SQL query :

SELECT COUNT(*) FROM (
    SELECT
        rec_business.personal_id,
        rec_business.id AS id,
        rec_personal.l_name AS last_name,
        rec_personal.f_name AS first_name,
        rec_personal.m_name AS middle_name,
        rec_business.bus_name AS bus_name,
        rec_business.reg_type AS reg_type,
        rec_business_type.name AS buss_type,
        rec_business.address AS address,
        rec_business.reg_date AS reg_date,
        rec_business.expire_date AS exp_date
    FROM
        rec_business
        INNER JOIN rec_personal ON rec_business.personal_id = rec_personal.id
        INNER JOIN rec_business_type ON rec_business_type.id = rec_business.bus_nature
    WHERE rec_business.expire_date <= NOW()
) t

How to combine these two queries in one

Upvotes: 1

Views: 65

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

sum() is the right function for what you intend instead of count() :

SELECT SUM(CASE WHEN rcb.expire_date > NOW() THEN 1 ELSE 0 END) AS NotExpired,
       SUM(CASE WHEN rcb.expire_date <= NOW() THEN 1 END) AS Expired,
       COUNT(1) As TotalCount
FROM rec_business rcb INNER JOIN 
     rec_personal rcp
     ON rcb.personal_id = rcp.id INNER JOIN 
     rec_business_type rbt
     ON rbt.id = rcb.bus_nature;

Upvotes: 0

Fred
Fred

Reputation: 5808

This is not tested but something like....

SELECT 
COUNT(CASE WHEN rec_business.expire_date > NOW() THEN 1 END) AS NotExpired,
COUNT(CASE WHEN rec_business.expire_date <= NOW() THEN 1 END) AS Expired,
COUNT(1) As TotalCount
FROM rec_business
    INNER JOIN rec_personal
        ON rec_business.personal_id = rec_personal.id
    INNER JOIN rec_business_type
        ON rec_business_type.id = rec_business.bus_nature

Upvotes: 3

Related Questions