How to Count Occurrences of a Column Value Efficiently in SQL

How to count occurrences of a column value efficiently in SQL?

This should work:

SELECT age, count(age) 
  FROM Students 
 GROUP by age

If you need the id as well you could include the above as a sub query like so:

SELECT S.id, S.age, C.cnt
  FROM Students  S
       INNER JOIN (SELECT age, count(age) as cnt
                     FROM Students 
                    GROUP BY age) C ON S.age = C.age

Count occurrences of a column value efficiently in SQL?

SELECT Car.make, Car.model, COUNT(Driver.id) AS drivers_count
FROM Driver
JOIN Car
ON Driver.car = Car.id
GROUP BY Car.make, Car.model

That's a single query. About performance, it looks perfectly fine to me, maybe you could explain us more in depth what's on your mind ?

How to count occurrences of certain column value efficiently in SQL?

You could use this:

SELECT 
    service_id,
    SUM(CASE WHEN service_status = 'download_started' THEN 1 ELSE 0 END) download_started,
    SUM(CASE WHEN service_status = 'download_failed' THEN 1 ELSE 0 END) download_failed
FROM 
    table_name
GROUP BY 
    service_id
ORDER BY 
    service_id;

Count number of occurrences for each unique value

Yes, you can use GROUP BY:

SELECT time,
       activities,
       COUNT(*)
FROM table
GROUP BY time, activities;

How to count occurrences of a column value and concat it to the same colums in SQL?

Use ROW NUMBER and CONCAT function to achieve the Same.

SELECT CONCAT(ID, '_', ROW_NUMBER() OVER (PARTITION BY SUBSTRING_INDEX(ID, '_', 1), Segment) ORDER BY ID) 
FROM Tablename

Best way to count occurrences in a table?

One idea is to select all the values into a union, then count:

something like:

SELECT data, count(*) FROM (
SELECT col1 as data FROM table
UNION ALL
SELECT col2 as data FROM table
...
) GROUP BY data

Count occurrences across columns in SQL Server

Try this:

Update Table1 set
    Occurrences = ISNUMERIC(jan) + ISNUMERIC(feb) + ISNUMERIC(mar) + ISNUMERIC(apr)

---

Related Topics