How do I select columns together with aggregate functions?

Question

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 '…

Accepted Answer

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.

How do I select columns together with aggregate functions?

Solution

Problem