CODEWITHSUNDEEP
sql-serveraggregate-functions
Kalessin
Kalessin

Reputation: 2302

How do I select columns together with aggregate functions?

Let's say I have a table of companies:

Company
coID | coName | coCSR

The coCSR field is a numeric ID which relates to the account handler table:

AccountHandler
ahID | ahFirstName | ahLastName

I also have a table of orders:

Order
orID | orCompanyID | orDate | orValue

Now what I need to produce is output structured as follows:

Company | Account handler | No. of orders | Total of orders

Here is the query I have tried, which produces an error:

SELECT coID, coName, ahFirstName+' '+ahLastName AS CSRName, COUNT(orID) AS numOrders, SUM(orValue) AS totalRevenue
FROM Company
LEFT JOIN AccountHandler ON coCSR = ahID
LEFT JOIN Order ON coID = orCompanyID
WHERE coCSR = 8
AND orDate > getdate() - 365
ORDER BY coName ASC

The error is: Column name 'AccountHandler.ahLastName' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.

If I use GROUP BY coID, I get Incorrect syntax near the keyword 'WHERE'. If I change the WHERE to HAVING because of the aggregate functions, I get errors telling me to remove each of the other column names that aren't contained in either an aggregate function or the GROUP BY clause.

I have to admit, I don't yet understand the logic and syntax of anything but the most basic SQL commands, I'm just trying to apply what I've seen used before, and it's not working. Please help me to get this working. Better still, can you help me understand why it doesn't work at the moment? :)

Upvotes: 6

Views: 43328

Answers (3)

Andriy M
Andriy M

Reputation: 77707

For one thing, your query is probably missing FROM Company, but that might somehow have been lost when you were writing your post.

You seem to be aggregating data by companies. Therefore you need to group by companies. The most likely reason why your attempt at grouping failed might be because you put GROUP BY in the wrong place. I think you put it before WHERE, but in fact it should go after it (and before ORDER BY):

SELECT
  c.coID,
  c.coName,
  a.ahFirstName + ' ' + a.ahLastName AS CSRName,
  COUNT(o.orID) AS numOrders,
  SUM(o.orValue) AS totalRevenue
FROM Company c
LEFT JOIN AccountHandler a ON c.coCSR = a.ahID
LEFT JOIN [Order] o ON c.coID = o.orCompanyID
WHERE c.coCSR = 8
AND o.orDate > getdate() - 365
GROUP BY ...
ORDER BY c.coName ASC

Another question is, what to group by. SQL Server requires that all non-aggregated columns be specified in GROUP BY. Therefore your GROUP BY clause should look like this:

GROUP BY
  c.coID,
  c.coName,
  a.ahFirstName,
  a.ahLastName

Note that you can't reference columns by aliases assigned to them in the SELECT clause (e.g. CSRName). But you could use the ahFirstName+' '+ahLastName expression instead of the corresponding columns, it wouldn't make any difference in this particular situation.

If you ever need to add more non-aggregated columns to this query, you'll have to add them both to SELECT and to GROUP BY. At some point this may become a bit tedious. I would suggest you try the following instead:

SELECT
  c.coID,
  c.coName,
  a.ahFirstName + ' ' + a.ahLastName AS CSRName,
  ISNULL(o.numOrders,    0) AS numOrders,
  ISNULL(o.totalRevenue, 0) AS totalRevenue
FROM Company c
LEFT JOIN AccountHandler a ON c.coCSR = a.ahID
LEFT JOIN (
  SELECT
    orCompanyID,
    COUNT(orID)  AS numOrders,
    SUM(orValue) AS totalRevenue
  FROM [Order]
  GROUP BY
    orCompanyID
  WHERE orDate > getdate() - 365
) o ON c.coID = o.orCompanyID
WHERE c.coCSR = 8
ORDER BY c.coName ASC

That is, aggregating is done on the Order table only. The aggregated row set is then joined to the other tables. You can now pull more attributes to the output from either Company or AccountHandler without worrying about adding them to GROUP BY because grouping is not needed at that level any more.

Upvotes: 5

whytheq
whytheq

Reputation: 35577

SELECT 
      --<<<non aggregate section of SELECT clause
     coID   
     , coName
     , [CSRName] = CONVERT(VARCHAR(100),ahFirstName + ' ' + ahLastName) 

      --<<<aggregate section of SELECT clause
     , [numOrders] = COUNT(orID)
     , [totalRevenue] = SUM(orValue) 
FROM   --<<<<<<sql is not too happy without FROM
    Company c
    LEFT JOIN AccountHandler a
       ON coCSR = ahID
    LEFT JOIN Order o
       ON coID = orCompanyID
WHERE coCSR = 8
   AND orDate > getdate() - 365
GROUP BY
     coID   
     , coName
     , CONVERT(VARCHAR(100),ahFirstName + ' ' + ahLastName) --<<<<looks like aggregate but is just text manipulation
ORDER BY coName ASC

You have two aggregate functions ; a COUNT and a SUM ; this means that you are required to do some grouping and the general rule of thumb is GROUP BY the non aggregate section of the select clause

The really big problem in your OP is that when you JOIN two tables, whatever flavour (LEFT, RIGHT, OUTER, INNER, CROSS) it has to be in the FROM clause and needs a table specified on either side of the join

Then if joining several tables you might like to use aliases for each of the tables; I've just used single lower case letter; c/o/a. Although looking at your column names these might not be needed as all columns are uniquely named.

Upvotes: 0

Pankaj
Pankaj

Reputation: 10105

Can you change the query like below? You should add Max and Group By Clause

SELECT 
        MAX(C.coID), 
        C.coName, 
        MAX(AH.ahFirstName+' '+ AH.ahLastName ) AS CSRName, 
        COUNT(O.orID) AS numOrders, 
        SUM(O.orValue) AS totalRevenue
From Company C
LEFT JOIN AccountHandler AH ON C.coCSR = AH.ahID
LEFT JOIN Order O ON C.coID = O.orCompanyID
WHERE   C.coCSR = 8 AND 
        O.orDate > getdate() - 365
Group by C.coName
ORDER BY C.coName ASC

My Suggestion

You should use Alias for the selected Column Names

Upvotes: 1

Related Questions