Menu
I am using mysql and php.
I have a table with one column. I can show unique rows by:
select distinct id from id_table
This might show
1
2
3
5
What I want to do is show the number of 1's, 2's, etc there are.
I could do
select count(id) from id_table where id = 1
But then I have to run a query for every... single... row... And with hundreds of thousands of rows, not good.
Is there a way to return an array of the counts of each distinct item
Data
1, 1, 1, 2, 3, 3, 5
Results
3, 1, 2, 1
Thanks
362
To count distinct values, you can use distinct in aggregate function count(). The result i3 tells that we have 3 distinct values in the table.
We can get the total number of rows in a table by using the MySQL mysqli_num_rows() function. Syntax: mysqli_num_rows( result ); The result is to specify the result set identifier returned by mysqli_query() function.
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 correct syntax for using COUNT(DISTINCT) is: SELECT COUNT(DISTINCT Column1) FROM Table; The distinct count will be based off the column in parenthesis. The result set should only be one row, an integer/number of the column you're counting distinct values of.
select id, count(id)
from table
group by id
If only want count of ids, then
select count(id)
from table
group by id
Here is a simple tutorial of group by clause
http://www.w3schools.com/sql/sql_groupby.asp
53
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
