Help with query

[arhicoc]

SELECT inspectors.id, inspectors.name, COUNT(persons.cnp) AS tickets_total,
SUM(CASE WHEN (dbo.persons.received = 1 AND (dbo.persons.cupon NOT IN (SELECT name FROM dbo.cupons))) THEN 1 ELSE 0 END) AS tickets_another_cupon
FROM inspectors INNER JOIN
persons ON inspector.id = persons.id_inspector
GROUP BY inspectors.id, inspectors.name ORDER BY inspectors.name

Message:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

How can I get this ?

I need to find the number of tickets for an inspector with a cupon other than those listed in the cupons table.

Another way
SELECT inspectors.id, inspectors.name, count(persons.cnp) as tickets_total,
(SUM(CASE WHEN persons.cupon IN ('Cupon January','Cupon February')
THEN 1 ELSE 0 END))
FROM inspectors INNER JOIN
persons ON inspectors.id = persons.id_inspector
GROUP BY inspectors.id, inspectors.name ORDER BY inspectors.name


Message
Column 'persons.cupon' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I do this
SELECT inspectors.id, inspectors.name, count(persons.cnp) as tickets_total,
(SUM(CASE WHEN persons.cupon IN ('Cupon January','Cupon February')
THEN 1 ELSE 0 END))
FROM inspectors INNER JOIN
persons ON inspectors.id = persons.id_inspector
GROUP BY inspectors.id, inspectors.name,persons.cupon ORDER BY inspectors.name

Every inspector is repeated for each type of cupon and that is not what I want.

[Alsvha]

The core of your problem is the GROUP BY.
You can't select things out in the SELECT clause which isn't in the GROUP BY clause or contained within an aggregated function (SUM, AVG, COUNT and so on).

The reason is that when grouping you - as the name indicates - group up multiple rows into one on the condition you set up. Therefor if you try to select something which isn't grouped on, you would have to select multiple rows out which then conflicts with the group by. But you can aggregate them because an aggregation is giving a result on multiple rows.
Hence your error message.


It will be difficult to point you in the right direction without knowing much of your database version, table layout and all that, so my recommendation will be to split the query up in smaller segments until you get a "base" which works, and then start adding one thing at the time.

If you're using SQL Server 2005+ take a look at the "Common Table Expression" structures and the ROW_Number() functions - I suspect they can help you quite a bit ....