Get count of multiple fields in MySQL

Question

I am unable to get 3 count fields from 3 different tables by using this SQL query below. Can someone help me out where I mistaken. The first count is assigning to another 2 fields. Means if I get 'total_scenarios' count as 10 the remaining 2 options(total_career_sketches, video_count) are taking same value 10.

$this->db->select('users.id,users.name,users.user_lname,users.email, 
count(fc_scenarios.user_id) as total_scenarios, count(career_sketch.user_id) 
as total_career_sketches, count(video_tracker.user_id) as video_count 
,career_timeline.filled_modules');
$this->db->from('users');
$this->db->join('fc_scenarios','users.id = fc_scenarios.user_id AND 
fc_scenarios.status = "A" AND fc_scenarios.type = "o"','left');
$this->db->join('career_sketch','users.id = career_sketch.user_id AND 
career_sketch.status = "A" AND career_sketch.type = "o"','left');
$this->db->join('video_tracker','users.id = video_tracker.user_id','left');
$this->db->join('career_timeline','users.id = 
career_timeline.user_id','left');
$this->db->where('users.inkwiry_user != ',1);
$this->db->where('users.status','A');
$this->db->group_by('users.id');

Thanks, Sateesh

user1173465 · Accepted Answer · 2018-06-27 13:20:05Z

2

You may need to count distinct values for your joined tables, I guess due to one to many relation you are getting multiple rows repeated rows because of joins, I suggest you to use distinct inside your count function like

$this->db->select('users.id,users.name,users.user_lname,users.email, 
count(distinct fc_scenarios.id) as total_scenarios, 
count(distinct career_sketch.id) as total_career_sketches, 
count(distinct video_tracker.id) as video_count,
career_timeline.filled_modules')

Also your query is invalid because group by clause has only one column o group rows but in select list your are trying to select other columns too which are not included in group by and neither aggregate

edited Jun 27, 2018 at 13:20

user1173465

531 silver badge7 bronze badges

answered Jun 27, 2018 at 6:39

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

4 Comments

user1173465 Over a year ago

When I update with distinct, I am getting the result as 0 or 1. And also if i remove group by, I am getting only one record from database.

M Khalid Junaid Over a year ago

@user1173465 Can you include sample data, table definitions and expected output in your question, Also group by is required but you need to use it correctly by including all non aggregated columns of select list in group by

user1173465 Over a year ago

Hi, I just attached one screenshot, can you please check that once.

M Khalid Junaid Over a year ago

@user1173465 it would be good if you could provide data set in text format not as image, I can't just extract data from image or can just use any online tool like sqlfiddle.com

viswakarthikreddy · Accepted Answer · 2018-06-27 12:12:51Z

You can try with following query, it will work

$this->db->select('users.id,users.name,users.user_lname,users.email, 
count(distinct fc_scenarios.id) as total_scenarios, count(distinct career_sketch.id) 
as total_career_sketches, count(distinct video_tracker.id) as video_count 
,career_timeline.filled_modules');
$this->db->from('users');
$this->db->join('fc_scenarios','users.id = fc_scenarios.user_id AND 
fc_scenarios.status = "A" AND fc_scenarios.type = "o"','left');
$this->db->join('career_sketch','users.id = career_sketch.user_id AND 
career_sketch.status = "A" AND career_sketch.type = "o"','left');
$this->db->join('video_tracker','users.id = video_tracker.user_id','left');
$this->db->join('career_timeline','users.id = 
career_timeline.user_id','left');
$this->db->where('users.inkwiry_user != ',1);
$this->db->where('users.status','A');
$this->db->group_by('users.id');

Collectives™ on Stack Overflow

Get count of multiple fields in MySQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related