bod1467 on May 21, 2007 at 5:48:27 AM (# 1)
This message has been edited.Try this ...
SELECT COUNT(orders.order_id) AS ordersTotal, users.*
FROM users
INNER JOIN orders on users.user_id = orders.order_user
GROUP BY users.user_name
ORDER BY users.user_name
vahid_reza on May 21, 2007 at 6:20:45 AM (# 2)Hi ,
Same error ! :(
it says that some fields in select list are not set in group by fields clause !
brian on May 21, 2007 at 6:32:49 AM (# 3)2 ways.
1: list all the fields from users and include them in the group by clause
2. use a sub-query. e.g.
select users.*,(select count(orders.order_id) from orders where orders.user_id=users.user_id) as counted
from users
this will work, but can be flaky with the likes of mysql (might be ok in newer versions) - it will definitely work with ms access and sql server
vahid_reza on May 21, 2007 at 2:55:45 PM (# 4)Hi
i did way 2 before post here ! in two words (IT SUCKS) . too slow !
and way 1 , there is about 12 fields , and more important thing , does it affect on result data ?
ChrisRickard on May 21, 2007 at 9:27:24 PM (# 5)What is your DBMS (Oracle, MSSQL, MySQL, etc)?
brian on May 22, 2007 at 1:30:26 AM (# 6)I use the second technique all the time and it is just fine. It can be slow if there are loads of records to count but as long as your indexes are optimised you shouldn't really notice it.
The first method doesn't affect results but it would probably also be slow as well if you have speed issues with the second one.
I'm assuming that you only wish to pull back users with orders...
ja928 on Jun 20, 2007 at 12:48:53 PM (# 7)A different twist on the subquery:
SELECT usr.*, ordersTotal
FROM_users usr
LEFT OUTER JOIN (SELECT order_user, COUNT(_orders.order_id) as ordersTotal FROM _orders GROUP BY order_user) ord on _users.user_id = ord.order_user
ORDER BY user_name
Also, Can you add a WHERE clause to limit the number of records you're counting in _orders?
| 
SQL COUNT() Help