Question 20079 in Mysql Question posted in
The official MySQL documentation can be found here.

Select the count of results in same table? – Mysql

Trying to find the items where origin_id is null and have it show the count where other rows in the same table have its id as origin_id set.

This query returns 0 for all 🙁 ?

SELECT 
    id,
    source_url,
    origin_id,
    (SELECT 
            COUNT(*)
        FROM
            queue_items
        WHERE
            queue_items.origin_id = queue_items.id) AS originCount
FROM
    queue_items
WHERE
    origin_id IS NULL
Tags:

2

Answers


  1. 0

    The subquery try to compare the originalid of the same table as id.

    you need aliases to differentiate the tables

    SELECT 
    id,
    source_url,
    origin_id,
    (SELECT 
            COUNT(*)
        FROM
            queue_items
        WHERE
            queue_items.origin_id = qi.id) AS originCount
FROM
    queue_items qi
WHERE
    origin_id IS NULL
    Login or Signup to reply.
  2. 0

    This can be done using self left join and group by :

    SELECT 
   t.id,
   t.source_url,
   t.origin_id,
   count(s.origin_id)
FROM queue_items t
LEFT JOIN queue_items as s on s.origin_id = t.id
WHERE t.origin_id IS NULL
group by t.id, t.source_url, t.origin_id

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search