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

by

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

More Related Contents:

  1. How does database indexing work? [closed]
  2. select * vs select column
  3. Inner join vs Where
  4. Best way to test if a row exists in a MySQL table
  5. Condition within JOIN or WHERE
  6. INNER JOIN vs LEFT JOIN performance in SQL Server
  7. Equals(=) vs. LIKE
  8. Subqueries vs joins
  9. Multiple INSERT statements vs. single INSERT with multiple VALUES
  10. Why would an IN condition be slower than “=” in sql?
  11. Optimise PostgreSQL for fast testing
  12. Does the order of where clauses matter in SQL?
  13. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  14. Normalize array subscripts so they start with 1
  15. Performance of inner join compared to cross join
  16. Counting DISTINCT over multiple columns
  17. Favourite performance tuning tricks [closed]
  18. SQL primary key: integer vs varchar
  19. SQL performance: WHERE vs WHERE(ROW_NUMBER)
  20. Can an INNER JOIN offer better performance than EXISTS
  21. How to check which locks are held on a table
  22. Huge performance difference when using GROUP BY vs DISTINCT
  23. SQL Server 2008 paging methods?
  24. Get execution time of PostgreSQL query
  25. Performance of Cross join with WHERE clause compared to Inner join
  26. When using GETDATE() in many places, is it better to use a variable?
  27. IN vs. JOIN with large rowsets
  28. What is the fastest way to truncate timestamps to 5 minutes in Postgres?
  29. IN vs OR of Oracle, which faster?
  30. sql query joins multiple tables – too slow (8 tables)

Leave a Comment