CODEWITHSUNDEEP
mysqlsql
Linesofcode
Linesofcode

Reputation: 5891

SQL Query to count sessions without repeating lines

I have two tables which join themselves by a field called user_id. The first table called sessions can have multiple lines for the same day. I'm trying to find a way of selecting the total of that sessions without repeating the days (sort of).

Example:

Table sessions
ID | user_id | datestart
1      1        2014-08-05
2      1        2014-08-05
3      2        2014-08-05

As you can see there are two lines that are repeated (the first and second). If I query SELECT COUNT(sess.id) AS total this will retrieve 3, but I want it to retrieve 2 because the first two lines have the same user_id so it must count as one.

Using the clause Group By will retrieve two different lines: 2 and 1, which is also incorrect.

You can view a full example working at SQLFiddle.

Is there anyway of solving this only by query or do I need to do it by language?

Upvotes: 1

Views: 53

Answers (3)

coder.in.me
coder.in.me

Reputation: 1068

If I understand the problem correctly, you want the number of users with sessions, rather than number of unique sessions. Use DISTINCT:

SELECT COUNT(DISTINCT(user_id)) FROM sessions,users WHERE user_id=users.id

Upvotes: 1

Robert
Robert

Reputation: 25753

Try this way:

SELECT COUNT(distinct sess.user_id) AS total
FROM sessions AS sess
INNER JOIN users AS user ON user.id = sess.user_id
WHERE user.equipment_id = 1 AND sess.datestart = CURDATE()

Sql Fiddle

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think you are looking for count(distinct):

SELECT COUNT(distinct user_id) AS total
FROM sessions sess INNER JOIN
     users user
     ON user.id = sess.user_id
WHERE user.equipment_id = 1 AND
      sess.datestart = CURDATE();

Upvotes: 2

Related Questions