Sql count returns wrong numbers

i have an api which i make calls to and i need alot of data from different tables so i use joins on them, now the problem is that whilst i have 4 replies, and 5 interactions the data always returns 20 replies and 20 interactions this is the result:

screen_name screen_state replies interactions alerts
sjerd       0            20      20           0

i use this query to count the records and results:

   SELECT u.screen_name,
          u.screen_state,
          count(r.id) AS replies,
          count(i.id) AS interactions,
          count(a.alerts) AS alerts
   FROM   users u 
   LEFT   JOIN reply r ON u.id = r.user 
   LEFT   JOIN interactions i ON u.id = i.user_id 
   LEFT   JOIN alerts a ON u.id = a.user_id WHERE u.id ='2' 
   GROUP  BY u.id, u.screen_state

can someone see why it's returning 20 while i only have 7 rows of replies in total in reply table, and 5 rows of interactions in total in interaction table. each row is 1 reaction or reply.

Answers


   SELECT u.screen_name, u.screen_state, 
   count(DISTINCT r.id) AS replies,
   count(DISTINCT i.id) AS interactions,
   count(DISTINCT a.alerts) AS alerts 
   FROM users u 
   LEFT JOIN reply r ON u.id = r.user 
   LEFT JOIN interactions i ON u.id = i.user_id 
   LEFT JOIN alerts a ON u.id = a.user_id WHERE u.id ='2' 
   GROUP BY u.id, u.screen_state

Your counts are always going to give the same result as all tables are joined at the same level.

You need to do your counts as inline sub-queries (or whatever - I can never remember the correct terminology):

SELECT u.screen_name,
       u.screen_state,
       (select count(*) from reply r where u.id = r.user) AS replies,
       (select count(*) from interactions i where u.id = i.user_id) AS interactions,
       (select count(*) from alerts a where u.id = a.user_id) AS alerts
FROM   users u
WHERE u.id ='2' 

Need Your Help

Ajax calendar extender not available despite script manager?

asp.net html ajax visual-studio

I've got a calendar extender on one page, and it works fine. Placed in panel with script manager, no problems. On another aspx page, however, using the same format, despite my script manager in the...

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.