SQL Join and getting count in table

Here is my SQL Fiddle

I am joining user and address table for now. Now i need to take the count of the logs table where in_time and out_time is filled

user

address

in_time

out_time

Here is my SQL Query that i have so far

select u.id, u.name, a.address from user u
left join address a on u.id = a.user_id
where u.id = 1


I.e., Output should be like this

id name address total_count proper_count
1 Alpha Chennai 4 3


varchar(50)

3 Answers
3

Use below schema design for your requirement. User_id must be INTEGER, and use datetime datatype for in & out time column.

CREATE TABLE log (
id BIGINT,
user_id BIGINT,
in_time datetime,
out_time datetime
);

INSERT INTO log (id,user_id, in_time, out_time) VALUES (1,1,'2018-07-21 06:50:41','2018-07-21 10:50:41');
INSERT INTO log (id,user_id, in_time, out_time) VALUES (2,1,'2018-07-22 06:50:41','2018-07-22 10:50:41');
INSERT INTO log (id,user_id, in_time) VALUES (3,1,'2018-07-23 06:50:41');
INSERT INTO log (id,user_id, in_time, out_time) VALUES (4,1,'2018-07-24 06:50:41','2018-07-22 10:50:41');

select u.id as user_id, u.name, a.address, COUNT(in_time) AS total_count, COUNT(out_time) as proper_count
from log l
INNER JOIN user u on u.id = l.user_id
INNER JOIN address a on a.user_id = u.id
GROUP BY u.id , u.name, a.address


You could you this:

SELECT u.id, u.name, a.address,
COUNT(*) AS total_count,
SUM(CASE WHEN l.in_time = 0 OR l.out_time = 0 THEN 0 ELSE 1 END) AS proper_count
FROM USER u
LEFT JOIN address a
ON u.id = a.user_id
LEFT JOIN log l
ON u.id = l.user_id
WHERE u.id = 1
GROUP BY u.id, u.name, a.address;


You were just one step back from your expected query. just need another join with log table and use aggregate function

select u.id, u.name, a.address,
sum(case when in_time is not null and out_time is not null
then 1 else 0 end ) as total_count ,
SUM(CASE WHEN l.in_time = 0 OR l.out_time = 0 THEN 0 ELSE 1 END) AS proper_count
from user u
left join address a on u.id = a.user_id
left join log l on u.id=l.user_id
where u.id = 1
group by u.id, u.name, a.address


http://sqlfiddle.com/#!9/b2efe0/6

id name address total_count proper_count
1 Alpha Chennai 4 3


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.