New to SQL. Really new...
The following code produces the desired (and correct) output:
SELECT
SUM (CASE WHEN Total < 2.00 THEN 1 ELSE 0 END) AS 'A',
SUM (CASE WHEN Total BETWEEN 2.00 and 6.99 THEN 1 ELSE 0 END) AS 'B',
SUM (CASE WHEN Total BETWEEN 7.00 AND 15.00 THEN 1 ELSE 0 END) AS 'C',
SUM (CASE WHEN Total > 15.00 THEN 1 ELSE 0 END) AS 'D'
FROM
invoices
ORDER BY
BillingCity;
A B C D
170 120 111 11
I want to add a column 'E' which should contain the grand total (412).
Not sure how to go about it. Yes I Googled. Saw some posts here but nothing that did what I am looking for.
I suspect you're looking for a solution that totals up A + B + C + D. And that might be something like...
with sub_totals as (
SELECT
SUM (CASE WHEN Total < 2.00 THEN 1 ELSE 0 END) AS 'A',
SUM (CASE WHEN Total BETWEEN 2.00 and 6.99 THEN 1 ELSE 0 END) AS 'B',
SUM (CASE WHEN Total BETWEEN 7.00 AND 15.00 THEN 1 ELSE 0 END) AS 'C',
SUM (CASE WHEN Total > 15.00 THEN 1 ELSE 0 END) AS 'D'
FROM
invoices
)
select
A, B, C, D,
A + B + C + D as 'E'
from sub_totals
However, your total range (almost) covers every possible value of Total. That is, E is just a count of rows: count as 'E'.
SELECT
SUM (CASE WHEN Total < 2.00 THEN 1 ELSE 0 END) AS 'A',
SUM (CASE WHEN Total BETWEEN 2.00 and 6.99 THEN 1 ELSE 0 END) AS 'B',
SUM (CASE WHEN Total BETWEEN 7.00 AND 15.00 THEN 1 ELSE 0 END) AS 'C',
SUM (CASE WHEN Total > 15.00 THEN 1 ELSE 0 END) AS 'D',
count() as 'E'
FROM
invoices
I said you almost cover every possible value of Total, but there's a subtle mistake. A Total of 6.995, for example, will not be counted.
SELECT
-- Up to, but not including, 2. Or [,2)
SUM (CASE WHEN Total < 2.00 THEN 1 ELSE 0 END) AS 'A',
-- From 2 up to 6.99. Or [2,6.99]
SUM (CASE WHEN Total BETWEEN 2.00 and 6.99 THEN 1 ELSE 0 END) AS 'B',
-- From 7 up to 15. Or [7,15]
SUM (CASE WHEN Total BETWEEN 7.00 AND 15.00 THEN 1 ELSE 0 END) AS 'C',
-- From, but not including, 15. Or (15,]
SUM (CASE WHEN Total > 15.00 THEN 1 ELSE 0 END) AS 'D',
This is a common problem with between. To get this right you need to use a mix of inclusive and exclusive bounds. Generally you want an inclusive lower bound, and an exclusive upper bound. This is sometimes written as [x,y).
SELECT
-- Up to, but not including, 2. Or [,2)
SUM (CASE WHEN Total < 2.00 THEN 1 ELSE 0 END) AS 'A',
-- From 2 up to, but not including, 7. Or [2,7)
SUM (CASE WHEN 2.00 <= Total and Total < 7.00 THEN 1 ELSE 0 END) AS 'B',
-- From 7 up to, but not including, 15. Or [7,15)
SUM (CASE WHEN 7.00 <= Total and Total < 15.00 THEN 1 ELSE 0 END) AS 'C',
-- From 15 up. [15,]
SUM (CASE WHEN 15.00 <= Total THEN 1 ELSE 0 END) AS 'D'
FROM
invoices