CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user2669043
user2669043

Reputation: 117

Need two calculated field but based on different where clause

I have a table that have lead amount, optyid, employee name and Quota.

I would like to write a query that allows me to get average lead amount and sum of quota by employee.

I thought it was easy, but the issue is that lead amount shows 0 for the rows that do not contain optyid, lead amount shows up as 0 as a placeholder. when I take average of all lead amount available, it's wrong as bunch of 0s are being included. To avoid such scenario, I wrote the following query.

SELECT
 employeename,
 avg(leadamount) AS [avglead],
 0 AS QUOTA
FROM TABLE
WHERE TABLE.optyid IS NOT NULL
GROUP BY employeename

UNION

SELECT
 employeename,
 0 AS [avglead],
 sum(quota) AS [QUOTA]
FROM TABLE 
WHERE TABLE.optyid IS NULL
GROUP BY employeename

Are there anyway to merge these together so that I only pull from TABLE once? The TABLE is so big that having two subqueries takes a long time to pull. I know I could probably use left join to the same table, but I don't think that will save much of time as it still needs to query TABLE twice.

I appreciate any inputs

Upvotes: 0

Views: 46

Answers (1)

Anon
Anon

Reputation: 10908

SELECT
 employeename,
 avg(CASE WHEN TABLE.optyid IS NULL THEN leadamount END) AS [avglead],
 sum(CASE WHEN TABLE.optyid IS NOT NULL THEN quota END) AS [QUOTA]
FROM TABLE
GROUP BY employeename

Upvotes: 1

Related Questions