MySQL with 2 tables

Question

I have 2 tables like this

Table PERSON

|NAME        |AGE       |
+------------+----------+
|mark        |20        |
|david       |18        |
|john        |35        |
|mary        |29        |
|sophie      |32        |

Table LOG:

|NAME        |ACTION      |TIME_STAMP            |
+------------+------------+----------------------+
|mark        |LOGIN       |2010-10-10 00:20:00   |
|mark        |LOGIN       |2010-10-10 00:25:00   |
|mark        |LOGIN       |2010-10-10 00:30:00   |
|mark        |LOGIN       |2010-10-10 00:35:00   |
|david       |LOGIN       |2010-10-10 00:30:00   |
|david       |LOGIN       |2010-10-10 00:35:00   |

And want this

|NAME        |AGE       |TOTAL ACTIONS |TOTAL TIME CONECTED |
+------------+----------+--------------+--------------------+
|mark        |20        |4             |00:15:00            |
|david       |18        |2             |00:05:00            |
|john        |35        |0             |00:00:00            | Or null
|mary        |29        |0             |00:00:00            | or null 
|sophie      |32        |0             |00:00:00            | or null

Actually I have this SQL but it doesn't show the people in first table who didn't login yet.

SELECT person.name, person.age, COUNT(DISTINCT log.time_stamp), SEC_TO_TIME(MAX(TIME_TO_SEC(time_stamp))- MIN(TIME_TO_SEC(time_stamp)))
FROM person, log
WHERE log.name = person.name
GROUP BY person.name;

Sadikhasan · Accepted Answer · 2014-04-04 08:14:29Z

2

Try using:

SELECT person.name, person.age, COUNT(DISTINCT log.time_stamp), 
       SEC_TO_TIME(MAX(TIME_TO_SEC(time_stamp))- MIN(TIME_TO_SEC(time_stamp)))
       FROM person
       LEFT JOIN log ON person.name = log.name
       GROUP BY person.name;

edited Apr 4, 2014 at 8:14

Sadikhasan

18.6k23 gold badges84 silver badges126 bronze badges

answered Apr 4, 2014 at 7:56

neel shah

2,27118 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL with 2 tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related