MySql Select Query Help - How to count number of times the query appeared

I currently have this query:

SELECT s_id FROM orderline,order_t WHERE orderline.o_id=order_t.o_id AND c_name='John Smith';

and it returns this:

+------+
| s_id |
+------+
|  12  |
+------+
|  11  |
+------+
|  10  |
+------+
|  10  |
+------+

but I want the output to have two columns, for the right column to count the number of times the left column appeared in the query.. so I want the output to be exactly like this one:

+------+-------+
| s_id | count |
+------+-------+
|  12  |   1   |
+------+-------+
|  11  |   1   |
+------+-------+
|  10  |   2   |
+------+-------+

Is it possible?

I tried this query, but it's clearly wrong as it counts the number of rows of s_id.

SELECT s_id,count(*) FROM orderline,order_t WHERE orderline.o_id=order_t.o_id AND c_name='John Smith';

Answers


You forgot the GROUP BY clause to have each "S_ID" on its own line

select S_ID, count(*) from ... group by S_ID


You need to use a GROUP BY:

SELECT s_id, count(*) AS `count`
FROM orderline,order_t 
WHERE orderline.o_id=order_t.o_id AND c_name='John Smith'
GROUP BY s_id;

Need Your Help

Spring JNDI, How do I tell the container where the beans are without code?

java spring jetty jndi

I have ben asked to make an enhancement to an older Spring application (I think 2.0 but maybe 2.5) and I know very little about Spring. The application runs under Oracle OC4J as it was given to me,...

Can't include iostream in C using MS Visual C++?

c struct iostream strcpy

I've been trying to learn C, and I'm stuck on including libraries. I need to use strcpy(), but that method is included in the iostream library, but whenever I try to include the library, the program