CODEWITHSUNDEEP
sqlsql-server
kazzi
kazzi

Reputation: 534

Getting values from two groupings

So I have a 500 line query, which I'll try and summarize in a simple query:

SELECT 
   max(t.USER_ID)
  ,max(t.OFFICE_ID)
  ,COUNT(CASE WHEN t.VALUE > 10 THEN 1 ELSE 0 END)
  /COUNT(t.VALUE) as Value_Avg

FROM(
   SELECT
     p.USER_ID
    ,c.OFFICE_ID
    ,h.VALUE
    , ... etc (Join, Where, ...)
   ) t
GROUP BY t.USER_ID

A query like that would give the following results:

USER_ID   OFFICE_ID   Value_Avg
1001      100         0.8
1002      100         0.75
1010      200         0.50

Basically showing the percentage of values greater than 10 by user.

Now, assuming User 1001 had 20 logs and user 1002 had 10 logs, and I wanted to also have a 4th column to average by department, how would I go ahead and do that if I could only use one group by?

Upvotes: 0

Views: 33

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You need calculate the Department average in a separated query. And then join with your current query to add the department average

WITH dept as (
   SELECT t.OFFICE_ID,
          COUNT(CASE WHEN t.VALUE > 10 THEN 1 ELSE 0 END)
        / COUNT(t.VALUE) as dept_Avg
   FROM ( ... ) t
   GROUP BY t.OFFICE_ID
)
SELECT 
    max(t.USER_ID)
  , max(t.OFFICE_ID)
  , COUNT(CASE WHEN t.VALUE > 10 THEN 1 ELSE 0 END)
   / COUNT(t.VALUE) as Value_Avg    
  , MAX( d.dept_Avg ) as dept_Avg
FROM ( ... ) t
JOIN dept d
  ON t.OFFICE_ID = d.OFFICE_ID
GROUP BY t.USER_ID

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You seem to want:

SELECT p.USER_ID, c.OFFICE_ID,
       AVG(CASE WHEN t.Value > 10 THEN 1.0 ELSE 0 END) as value_avg
FROM . . .
GROUP BY p.USER_ID, c.OFFICE_ID;

You don't need a subquery. This assumes that Value is never NULL. If it is, then change the expression to:

       AVG(CASE WHEN t.Value > 10 THEN 1.0
                WHEN t.Value IS NOT NULL THEN 0
           END) as value_avg

Upvotes: 1

Related Questions