How to join two queries counting from two different columns?

Question

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

Denisa · Accepted Answer · 2016-01-19 19:12:21Z

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

singhsac · Accepted Answer · 2016-01-19 19:53:53Z

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)

Fuzzy · Accepted Answer · 2016-01-19 18:53:39Z

0

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

edited Jan 19, 2016 at 18:53

answered Jan 19, 2016 at 18:42

Fuzzy

3,8102 gold badges17 silver badges34 bronze badges

1 Comment

Fuzzy Over a year ago

@Rahul I assumed we only want Distinct retruned. But you are correct if we want complete set from both statements then UNION ALL

singhsac · Accepted Answer · 2016-01-19 19:35:16Z

0

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

edited Jan 19, 2016 at 19:35

singhsac

5011 gold badge8 silver badges17 bronze badges

answered Jan 19, 2016 at 18:47

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

3 Comments

anatp_123 Over a year ago

@vjp it seems this ignores the submitterdept column count

Vamsi Prabhala Over a year ago

just noticed and modified the query. Note that if there are nulls in the submitterdept they would be ignored when using count.

anatp_123 Over a year ago

theres no nulls in any of the two columns, but it only seems to get the correct numbers for totalstars, totalstargiven are wrong

Collectives™ on Stack Overflow

How to join two queries counting from two different columns?

4 Answers 4

Comments

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related