CODEWITHSUNDEEP
mysqlsqlcount
Toquis
Toquis

Reputation: 115

SQL count problems

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

Upvotes: 0

Views: 76

Answers (3)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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

Upvotes: 0

Maryam Arshi
Maryam Arshi

Reputation: 2024

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

Upvotes: 0

Rowland Shaw
Rowland Shaw

Reputation: 38130

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

Upvotes: 1

Related Questions