MySQL Query with Counts in columns

Question

I have two tables l & C which are linked by two fields l.mID and c.mID the c table holds three types of records cs, ls, & vs with my query I need to select all records from l and as part of the query have three columns showing totals for cs, ls & vs;

SO far I am using an outer join:

SELECT l.*, COUNT(*) FROM l LEFT OUTER JOIN c ON c.mID = l.mID WHERE l.mID = 2

But this just appends one column with a full total any help would be great thanks.

It might be useful in this case to show sample data for the tables and to explain what aggregation you are attempting to do. — Mike Brant
– Mike Brant, Commented Jul 24, 2012 at 15:13
As per juergen below this is what I would like to achieve however I am getting multi plicated values 5 is bemiring 25 any reason for this? — Justin Erswell
– Justin Erswell, Commented Jul 25, 2012 at 15:53

mellamokb · Accepted Answer · 2012-07-24 15:13:17Z

2

You can use a CASE statement in your count:

select
  l.*
  ,COUNT(CASE WHEN c.type = 'cs' then 1 END) as cscount
  ,COUNT(CASE WHEN c.type = 'ls' then 1 END) as lscount
  ,COUNT(CASE WHEN c.type = 'vs' then 1 END) as vscount
from l
left outer join c on c.mID = l.mID
where l.mID = 2

answered Jul 24, 2012 at 15:13

mellamokb

56.8k12 gold badges111 silver badges138 bronze badges

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

1 Comment

Justin Erswell Over a year ago

Thanks buddy really helpful and a great solution to my issue :)

juergen d · Accepted Answer · 2012-07-24 15:16:56Z

1

select
  l.*
  ,sum(c.type = 'cs') as cscount
  ,sum(c.type = 'ls') as lscount
  ,sum(c.type = 'vs') as vscount
from l
left outer join c on c.mID = l.mID
where l.mID = 2

answered Jul 24, 2012 at 15:16

juergen d

205k40 gold badges303 silver badges377 bronze badges

1 Comment

Justin Erswell Over a year ago

Thanks for this however it seems to multiply the values i.e. 5 becomes 25 any thoughts? I have repeating lines in the l table as it is a view

Collectives™ on Stack Overflow

MySQL Query with Counts in columns

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related