Search code examples
sqlsql-servert-sqlcounting

Count Records from One Column WHERE values are different


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 COUNTs 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.


Solution

  • 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