I'm fairly new to SQL-server and stack overflow, so forgive me if my question is misworded, or naive.
I tried searching but couldn't find exactly what I was looking for.
I have a table with a column unique identifier (varchar 50)
, and another column1 of INT
which has values of 1, 2, 3, or 4. I am trying to create a SELECT
resultset which COUNT
s all of the records which have values less than 4 in column1 and another COUNT
which returns the number of records where column1 equals 4. Is it possible to create this resultset in one query?
Ideally, I'd like my resultset to look like the following:
Total Records Records w/ *column1* < 4 Records w/ *column1* = 4
=========== ================== ================
1000 850 150
After completing some searches, I've tried the following:
SELECT COUNT(unique_id) 'Total'
, COUNT(CASE
WHEN column1 = 4 THEN '1'
ELSE '0'
END) AS 'Records w/ 4'
, COUNT(CASE
WHEN column1 < 4 THEN '1'
ELSE '0'
END) AS 'Records < 4'
FROM mytable
;
Which returns a result set where 'Record w/ 4' and 'Total' both equal the same number.
Try this-
SELECT COUNT(*) 'Total'
, SUM(CASE WHEN column1 = 4 THEN 1 ELSE 0 END) AS 'Records w/ 4'
, SUM(CASE WHEN column1 < 4 THEN 1 ELSE 0 END) AS 'Records < 4'
FROM mytable