CODEWITHSUNDEEP
mysqlunion
EmmyS
EmmyS

Reputation: 12138

How can I use count() to return a single record with totals from multiple tables (union)?

SELECT COUNT( np.id ) FROM new_people np
LEFT JOIN institute.users_roles ur ON np.institute_uid = ur.uid
LEFT JOIN roster r ON np.id = r.people_id
WHERE np.company_id =55923 AND ur.rid =8 AND np.active =1 AND r.roster_id IS NULL  

UNION 

SELECT COUNT( people_id ) FROM roster
WHERE lu =1 AND STATUS <>  'complete' AND company_id =55923

new_people has 15 records that meet the select criteria. roster has 12. If I run this query as-is, it returns two rows, one containing "15" and the other containing "12". Is there any way to write this so it returns a single value of "27"?

Upvotes: 1

Views: 103

Answers (2)

Nonym
Nonym

Reputation: 6299

The easy way:

SELECT SUM(T1.COL1) FROM (
    SELECT COUNT( np.id ) AS COL1 FROM new_people np
    LEFT JOIN institute.users_roles ur ON np.institute_uid = ur.uid
    LEFT JOIN roster r ON np.id = r.people_id
    WHERE np.company_id =55923 AND ur.rid =8 AND np.active =1 AND r.roster_id IS NULL  

    UNION ALL 
    -- Added ALL to prevent DISTINCT from being put into effectivity by the UNION
    -- ; thanks for the reminder, @nickrulez

    SELECT COUNT( people_id ) AS COL1 FROM roster
    WHERE lu =1 AND STATUS <>  'complete' AND company_id =55923
) T1

Unless there is some way.. to join.. the second SELECT statement with the first.. At the moment, I don't see how, though.. But if there is.. let us know..

Upvotes: 3

Nicola Cossu
Nicola Cossu

Reputation: 56357

select sum(tot) as total from (
SELECT COUNT( np.id ) as tot FROM new_people np
LEFT JOIN institute.users_roles ur ON np.institute_uid = ur.uid
LEFT JOIN roster r ON np.id = r.people_id
WHERE np.company_id =55923 AND ur.rid =8 AND np.active =1 AND r.roster_id IS NULL  

UNION ALL

SELECT COUNT( people_id ) FROM roster
WHERE lu =1 AND STATUS <>  'complete' AND company_id =55923) as t

Upvotes: 2

Related Questions