Using count in a subquery and getting errors

Question

I have data on a row in the table, and I need to disconnect from the sale, the sale and create a group of reports by the schema identifier. ie I need to find all sales and subtract all cancellations to get a net sales figure.

I am trying to use the query below, but I am 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 get an 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 with this?

Answer 1

You don't need any subqueries here. Just use COUNTand 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 assumed above that it is cancellationnot NULL. If used

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

Answer 2

, .

select  insscheme, SUM(sale) - SUM(cancellation) as NetSales
from    policys
group by insscheme

, quote_id . ? , , , .