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