Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select count for each distinct row (mysql and php)

Tags:

php

select

mysql

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

like image 362
Jason Pawlak Avatar asked Nov 25 '11 03:11

Jason Pawlak


People also ask

How do I count distinct values in MySQL?

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.

How can I count rows in PHP?

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.

How do I count the number of distinct values in SQL?

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.

How do you use count and distinct together?

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.


1 Answers

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

like image 53
Zohaib Avatar answered Oct 09 '22 10:10

Zohaib