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

Windows Equivalent of System Configuration Directory

windows ruby configuration command-line configuration-files

I am developing a CLI application in Ruby, and I'd like to allow configuration in Unix via the standard config file cascade of /etc/appnamerc, ~/.appnamerc. However, the application is also meant t...

How to fix Lock wait timeout?

java mysql

I am using glassfish 3.1.2 and MySQL. when running my application i am seeing the below error:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.