none
Use Sum In Case Statement RRS feed

  • Question

  • Greetings - I am trying to execute the below query and I need to do it all in subqueries as I can only have one table in the from (silly requirement I understand, but that's what I am working with).

    How would I alter this query so that I do not get the error:

    Cannot perform an aggregate function on an expression containing an aggregate or subquery

    Select
    emp.id
    ,emp.fname
    ,emp.lname
    ,emp.address1
    ,emp.address2
    ,emp.address3
    ,emp.city
    ,emp.state
    ,emp.zip
    ,emp.phone
    ,TotalSales = (Select Count(saleID) From registers rg where emp.ID = rg.empID)
    ,SMetrics = (Select case sc.ars when 1 then Sum(Round(sd.amt),02) else NULL end From salescatalog sc Left Join salesdata sd on sc.arID = sd.salescat Group By sc.ars)
    FROM employeeInfo emp

    Thursday, January 9, 2020 5:14 PM

Answers

  • Select emp.id ,emp.fname ,emp.lname ,emp.address1 ,emp.address2 ,emp.address3 ,emp.city ,emp.state ,emp.zip ,emp.phone

    ,TotalSales = (Select Count(saleID) From registers rg where emp.ID = rg.empID)
     ,SMetrics = (Select case sc.ars when 1 then Sum(Round(sd.amt,2)) else NULL end From salescatalog sc Left Join salesdata sd on sc.arID = sd.salescat Group By sc.ars)
    FROM employeeInfo emp

    • Marked as answer by IndigoMontoya Thursday, January 9, 2020 5:27 PM
    Thursday, January 9, 2020 5:23 PM
    Moderator

All replies

  • E.g.

    SELECT emp.id ,
           emp.fname ,
           emp.lname ,
           emp.address1 ,
           emp.address2 ,
           emp.address3 ,
           emp.city ,
           emp.state ,
           emp.zip ,
           emp.phone ,
           (   SELECT COUNT(saleID)
               FROM   registers rg
               WHERE  emp.ID = rg.empID ) AS TotalSales ,
           (   SELECT   ROUND(SUM(CASE sc.ars
                                       WHEN 1 THEN sd.amt
                                       ELSE 0
                                  END) ,
                              02)
               FROM     salescatalog sc
                        LEFT JOIN salesdata sd ON sc.arID = sd.salescat
               GROUP BY sc.ars ) AS SMetrics
    FROM   employeeInfo emp;

    Thursday, January 9, 2020 5:23 PM
  • Select emp.id ,emp.fname ,emp.lname ,emp.address1 ,emp.address2 ,emp.address3 ,emp.city ,emp.state ,emp.zip ,emp.phone

    ,TotalSales = (Select Count(saleID) From registers rg where emp.ID = rg.empID)
     ,SMetrics = (Select case sc.ars when 1 then Sum(Round(sd.amt,2)) else NULL end From salescatalog sc Left Join salesdata sd on sc.arID = sd.salescat Group By sc.ars)
    FROM employeeInfo emp

    • Marked as answer by IndigoMontoya Thursday, January 9, 2020 5:27 PM
    Thursday, January 9, 2020 5:23 PM
    Moderator