SQL - Counting Rows with Specific Value

SQL - counting rows with specific value

With MySQL, you can use SUM(condition):

SELECT   id, SUM(value=0) AS n0, SUM(value=1) AS n1, COUNT(*) AS total
FROM     mytable
GROUP BY id

See it on sqlfiddle.

SQL count rows where column value contains string

Use a case expression:

select sum(case when column_name like '%substring%' then 1 else 0 end)
from table_name ;

Or, if that is the only value you want, use filtering and count(*):

select count(*)
from table_name
where column_name like '%substring%';

The first is more suitable when you have additional columns in the query.

I should note that the SQL standard has another method of accomplishing this, using filter:

select count(*) filter (where column_name like '%substring%')
from table_name ;

However, most databases do not support this syntax.

T-SQL Count rows with specific values (Multiple in one query)

SELECT IGrp, 
    COUNT(CASE WHEN Value1 > 1 THEN 1 ELSE NULL END) AS V1High, 
    COUNT(CASE WHEN Value2 > 1 THEN 1 ELSE NULL END) AS V2High 
FROM Tbl
GROUP BY IGrp

Count records in table and show line number of specific value

Please find the query with the window function:

DECLARE @intPos AS INT = 0;

;WITH RowCountCte AS (
   SELECT ID, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS intPos
   FROM tblData
)

SELECT @intPos = intPos FROM RowCountCte WHERE ID = 101;

SELECT COUNT(ID) AS cntRec, @intPos AS intPos
FROM tblData;

Demo on db<>fiddle

How to count rows in a table, where a specific column value is in a list of specified values?

I would suggest using the IN operator, to write it in a more concise manner:

Count (Calculate) = 
CALCULATE (
    COUNTROWS ( dCustomers ) ,
    dCustomers[customerType] IN {"FR", "DE", "GG"}
)

You can also use COUNTROWS directly on the filtered table, this looks to be marginally faster from a bit of testing on a random dataset, but please do benchmarking on a case by case basis:

Count (Filter) = 
COUNTROWS ( 
    FILTER (
        dCustomers ,
        dCustomers[customerType] IN {"FR", "DE", "GG"}
    )
)

---

Related Topics