Select Count of Rows with Joined Tables

15,044

Solution 1

You can treat the searched_domains rows that have is_basedomain=1 as a separate table in the query and join it with another instance of searched_domains (to get the count):

SELECT 
    d.tablekey_id, 
    k.linkdata_id, 
    k.timestamp,  
    d.domain, 
    d.is_basedomain, 
    COUNT(*) as 'count'
FROM 
    tablekey AS k
      join searched_domains AS d on d.tablekey_id=k.id
      join searched_domains AS d2 on d2.tablekey_id=d.tablekey_id
WHERE 
    d.is_basedomain = 1 
GROUP BY 
    d.tablekey_id, 
    k.linkdata_id, 
    k.timestamp,  
    d.domain, 
    d.is_basedomain

Solution 2

you have an error when using ON instead use WHERE

try this

    SELECT `tablekey_id`, `linkdata_id`, `timestamp`, `domain`, `is_basedomain`,
    (SELECT COUNT(1) AS other FROM `searched_domains` AS dd 
    where dd.tablekey_id = d.tablekey_id GROUP BY `tablekey_id`) AS count

    FROM `tablekey` AS k 
    JOIN `searched_domains` AS d
    ON k.id = d.tablekey_id 
    WHERE `is_basedomain` = 1 GROUP BY `tablekey_id`

DEMO HERE

Solution 3

There is no reason to use subquery, or what is your opinion?

SELECT 
    `tablekey_id`, 
    `linkdata_id`, 
    `timestamp`,  
    `domain`, 
    `is_basedomain`, 
    COUNT(*) as count
FROM 
    `tablekey` AS k , 
    `searched_domains` AS d
WHERE 
    k.id = d.tablekey_id AND 
    `is_basedomain` = 1 
GROUP BY 
    `tablekey_id`, 
    `linkdata_id`, 
    `timestamp`, 
    `domain`,
    `is_basedomain`

If you want only latest timestamp use MAX(timestamp) as timestamp and remove it from group by.

Share:
15,044
RachelD
Author by

RachelD

I’m a software engineer and I pride myself on my ability to learn new technologies fast. I have experience with more than a dozen languages, database and version control systems. Additionally I have developed and launched numerous content management platforms. My goal is to expand my programming knowledge and become a recognized expert in my field. Want to know more? LinkedIn > Rachel L Dotey

Updated on November 23, 2022

Comments

  • RachelD
    RachelD less than a minute

    I have two tables with a one to many relationship. I join the tables by an id column. My problem is that I need a count of all matching entries from the second (tablekey_id) table but I need the information from the row marked with the boolean is_basedomain. As a note there is only one row with is_basedomain = 1 per set of rows with the same tablekey_id.

    Table: tablekey

    id  linkdata_id  timestamp
    22  9495028175   2013-03-10 01:13:46
    23  8392740179   2013-03-10 21:23:25
    

    Table: searched_domains.

    NOTE: tablekey_id is the foreign key to the id in the tablekey table.

    id  tablekey_id  domain        is_basedomain
    1   22           somesite.com  1
    2   22           yahoo.com     0
    3   23           red.com       1
    4   23           blue.com      0
    5   23           green.com     0
    

    Heres the query Im working with. I was trying to use a sub query but I cant seem to select only the count for the current tablekey_id so this does not work.

    SELECT `tablekey_id`, `linkdata_id`, `timestamp`, `domain`, `is_basedomain`,
       (SELECT COUNT(1) AS other FROM `searched_domains` AS dd 
        ON dd.tablekey_id = d.tablekey_id GROUP BY `tablekey_id`) AS count
    FROM `tablekey` AS k 
    JOIN `searched_domains` AS d
    ON k.id = d.tablekey_id 
    WHERE `is_basedomain` = 1 GROUP BY `tablekey_id`
    

    The result that I would like to get back is:

    tablekey_id  linkdata_id  timestamp            domain        is_basedomain    count
    22           9495028175   2013-03-10 01:13:46  somesite.com  1                2
    23           8392740179   2013-03-10 21:23:25  red.com       1                3
    

    Can anyone help me get this into one query?

  • slaakso
    slaakso over 9 years
    Will not produce a correct result as the searched_domains is limited to only those rows where is_basedomain=1. This will produce incorrect result to count column.
  • iiro
    iiro over 9 years
    @slaakso i dont understand. I tried your and mine queries in sql fiddle sqlfiddle.com/#!2/fdecf/3
  • iiro
    iiro over 9 years
    Ok perhaps i understood now after the question was edited. Your and echo_me's query gives different results. I'll check what's going on :)