CODEWITHSUNDEEP
sql
anatp_123
anatp_123

Reputation: 1205

How to join two queries counting from two different columns?

I have two queries that I'm grouping to get the account.

Is it possible make it in one query using the dept_id column, some of the ID's may no exist in both queries.

The output like this: 

dept_id | totalstars | totalstarsgiven

Query:

SELECT  
    employeedept as dept_id,
    COUNT(*) as 'totalstars'
FROM 
    Responses a 
WHERE
    execoffice_status = 1
    AND YEAR ([approveddate]) = 2015
    AND MONTH ([approveddate]) = 11 
    and employeedept not in (22,16) 
GROUP BY 
    execoffice_status, employeedept

SELECT 
    a.submitterdept as dept_id,
   COUNT(*) as 'totalstarsgiven'
FROM 
    Responses a 
WHERE 
    execoffice_status = 1
    AND YEAR ([approveddate]) = 2015
    AND MONTH ([approveddate]) = 11
GROUP BY 
    execoffice_status, submitterdept

Upvotes: 0

Views: 92

Answers (4)

Ctx
Ctx

Reputation: 18420

I think this will do what you want:

SELECT
    employeedept as dept_id
    , COUNT(*) as totalstars
    , totalstarsgiven
FROM
    Responses a
    LEFT JOIN (
        SELECT
            a.submitterdept as dept_id
            , COUNT(*) as totalstarsgiven
        FROM
            Responses a
        WHERE
            execoffice_status = 1
            and YEAR ([approveddate]) =2015
            and month ([approveddate]) =11
        GROUP BY 
            execoffice_status
            , submitterdept
    ) b
        ON a.employeedept = b.dept_id
WHERE
    execoffice_status = 1
    and YEAR ([approveddate]) =2015
    and month ([approveddate]) =11 
    and employeedept not in (22,16)
GROUP BY 
    execoffice_status
    , employeedept

This will aggregate the departments by the number of stars they received (when dept_id = employeedept) and given (when dept_id = submitterdept)

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This can be done in one query using conditional aggregation.

SELECT 
    employeedept as dept_id,
    sum(case when employeedept not in (22,16) then 1 else 0 end) as totalstars,
    count(submitterdept) as totalstarsgiven
FROM 
    Responses
WHERE 
    execoffice_status = 1 and YEAR([approveddate]) = 2015 and month([approveddate]) = 11  
GROUP BY 
    employeedept

Upvotes: 0

Denisa
Denisa

Reputation: 330

Because you want to see the lines returned by both tables, you need to do a Full Outer Join.

 SELECT NVL(ed.dept_id, sd.dept_id), NVL(ed.totalstars, 0) totalstars,
 NVL(sd.totalstarsgiven, 0) totalstarsgiven 
 FROM
      (SELECT  employeedept as dept_id, COUNT(*) as totalstars
         FROM Responses a    
         WHERE execoffice_status = 1
         and YEAR ([approveddate]) =2015
         and month ([approveddate]) =11 
         and employeedept not in (22,16) 
         GROUP BY execoffice_status, employeedept) ed
 FULL OUTER JOIN 
      (SELECT  a.submitterdept as dept_id, COUNT(*) as totalstarsgiven
         FROM Responses a    
         WHERE execoffice_status = 1
         and YEAR ([approveddate]) =2015
         and month ([approveddate]) =11
         GROUP BY execoffice_status, submitterdept) sd 
 ON ed.deptId = sd.deptId

Upvotes: 1

Fuzzy
Fuzzy

Reputation: 3810

Is this what you are after?

SELECT  employeedept as dept_id, COUNT(*) as 'totalstarsgiven'
        FROM Responses a    
        WHERE execoffice_status = 1
        and YEAR ([approveddate]) =2015
        and month ([approveddate]) =11 
        and employeedept not in (22,16) 
        GROUP BY execoffice_status, employeedept
UNION
SELECT  a.submitterdept as dept_id, COUNT(*) as 'totalstarsgiven'
        FROM Responses a    
        WHERE execoffice_status = 1
        and YEAR ([approveddate]) =2015
        and month ([approveddate]) =11
        GROUP BY execoffice_status, submitterdept

or you can try :

SELECT  CASE WHEN a.employeedept not in (22,16) THEN employeedept ELSE  a.submitterdept END as dept_id, COUNT(*) as 'totalstarsgiven'
        FROM Responses a    
        WHERE execoffice_status = 1
        and YEAR ([approveddate]) =2015
        and month ([approveddate]) =11
        GROUP BY execoffice_status, CASE WHEN a.employeedept not in (22,16) THEN employeedept ELSE  a.submitterdept END

Upvotes: 0

Related Questions