using count in a subquery and getting errors

Question

i have line by line data in a table and i need to net of cancellations from sales and produce a report grouping on a scheme identifier. i.e i need to find all the sales and subtract all the cancellatsion to prduce a net sales figure.

i am trying to use the query below but i'm getting errors.

select insscheme, ((select count(quote_id) where (sale = '1')) - (select count(quote_id) where cancellation = '1')) as sales from policys group by insscheme order by insscheme

and i'm getting the error

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

Can anyone help me out with this?

Answer 1

You don't need any sub queries here. Just use COUNT and CASE.

SELECT   insscheme,
         COUNT(CASE WHEN sale = '1' AND cancellation <> '1' THEN 1 END) AS sales
FROM     policys
GROUP BY insscheme
ORDER BY insscheme

I have assumed above that cancellation is not nullable. If it is use

COUNT(CASE WHEN sale = '1' THEN 1 END) -  
COUNT(CASE WHEN cancellation = '1' THEN 1 END) AS sales