SQL count problems

Question

I have a query problem with count. I want to have a column with the number of persons registered to the course.

So far, this is my query:

select
  courses.id, 
  name, 
  location, 
  capacity, 
  (
    SELECT count(courses_requests.IDcourse) 
    FROM courses_requests, courses
    WHERE courses_requests.IDcourse = courses.id AND status != "rejected"
  ) as Registered, 
  begin_date, 
  end_date,  
  price, 
  active
from courses

But this is giving me problems, it displays the same value for all rows, even if the course doesn't have persons registered in the course

E.G

Capacity Registered
2        1
30       1

Rowland Shaw · Accepted Answer · 2014-02-21 21:06:55Z

1

It may be simplier to aggregate the outer select, to eliminate the subquery, so something like:

SELECT      c.id, 
            c.name, 
            c.location, 
            c.capacity, 
            COUNT(cr.IDcourse) AS RequestCount 
            c.begin_date, 
            c.end_date,  
            c.price, 
            c.active

FROM        courses c

INNER JOIN  courses_requests cr
    ON      cr.IDcourse = c.id 
    AND     status != "rejected"

GROUP BY    c.id, 
            c.name, 
            c.location, 
            c.capacity, 
            c.begin_date, 
            c.end_date,  
            c.price, 
            c.active

answered Feb 21, 2014 at 21:06

Rowland Shaw

38.2k15 gold badges104 silver badges168 bronze badges

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

1 Comment

Toquis Over a year ago

Thank, this works!. Although I had to edit it a little bit. I changed the inner join to left join and an ifnull function in the count to return 0

Maryam Arshi · Accepted Answer · 2014-02-21 21:07:34Z

0

You should connect your subquery to main query:

select courses.id, 
    courses.name, 
    courses.location, 
    courses.capacity, 
    (SELECT count(courses_requests.IDcourse) 
          FROM    courses_requests, 
           WHERE courses_requests.ID = courses.id 
            and status != "rejected" ) as Registered, 
    begin_date, 
    end_date,  
    price, 
    active
    from courses

answered Feb 21, 2014 at 21:07

Maryam Arshi

2,0342 gold badges20 silver badges34 bronze badges

Comments

M Khalid Junaid · Accepted Answer · 2014-02-21 21:07:50Z

0

You can use join to simplify your query ans using SUM() with a condition will give you the count

select
  c.id, 
  c.name, 
  c.location, 
  c.capacity, 
  SUM(cr.status != "rejected") as Registered, 
  c.begin_date, 
  c.end_date,  
  c.price, 
  c.active
from courses c
JOIN courses_requests cr ON (cr.IDcourse = c.id)
GROUP BY c.id

answered Feb 21, 2014 at 21:07

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Collectives™ on Stack Overflow

SQL count problems

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related