Menu
I have a table of students:
id | age -------- 0 | 25 1 | 25 2 | 23 I want to query for all students, and an additional column that counts how many students are of the same age:
id | age | count ---------------- 0 | 25 | 2 1 | 25 | 2 2 | 23 | 1 What's the most efficient way of doing this? I fear that a sub-query will be slow, and I'm wondering if there's a better way. Is there?
737
To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.
The simple answer is no – there is no difference at all. The COUNT(*) function counts the total rows in the table, including the NULL values.
According to this theory, COUNT(*) takes all columns to count rows and COUNT(1) counts using the first column: Primary Key. Thanks to that, COUNT(1) is able to use index to count rows and it's much faster.
So to make SELECT COUNT(*) queries fast, here's what to do:Get on any version that supports batch mode on columnstore indexes, and put a columnstore index on the table – although your experiences are going to vary dramatically depending on the kind of query you have.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
