CODEWITHSUNDEEP
mysqlsql
Karem
Karem

Reputation: 18103

Count the same order_id and output how many in SQL

I would like to count how many rows there is of the same order_id in the table refnumbers

So it should output:

There's 5 rows with the order_id 123

There's 9 rows with the order_id 124

There's 18 rows with the order_id 125

There's 2 rows with the order_id 77

It's the column order_is that it should counter after in the table refnumbers

I dont really know how to do this without specifically mention a order_id and then do a loop through them in php.

Upvotes: 1

Views: 87

Answers (1)

Johan
Johan

Reputation: 76567

You need to use group by on the column that you want to get the count for.

This does not work 

SELECT count(*) as rowcount
FROM refnumbers

Will give you a single row with all rowcounts

Count per distinct order_id 

SELECT order_id, count(*) as rowcount
FROM refnumbers
GROUP BY order_id /*WITH ROLLUP*/
ORDER BY order_id

Will give you the count per distinct order_id.
If you want to get the total count as well uncomment the with rollup part.

Upvotes: 4

Related Questions