Select and Count in One Query from Two Tables With MySQL

mysql count records from two tables in one query?

I think this should work assuming your tsId and paId are unique keys:

SELECT Count(DISTINCT t.tsID) AS tsCount, 
    Count(DISTINCT p.paID) AS paCount
FROM account a 
    LEFT JOIN test t ON a.acId = t.tsAccountId
    LEFT JOIN patient p ON a.acId = p.paAccountId
WHERE a.acId = 1

And here is the SQL Fiddle.

Please note: the problem with not joining on the account table (and using it as the master table) is that if either the test table or the patient table have no data for a specific account id, the query will return 0 results for each -- which could be incorrect.

Mysql Query select count(*) from multiple tables

Within the various selects, keep the same column order.

So

SELECT   a1.id as movie_id,
         a1.movie_title as movie_title,
         COUNT(*) AS free_cnt
         0 as plus_cnt,
         0 as visitor_cnt
FROM     movie_request a1
GROUP BY a1.movie_title

UNION ALL

SELECT   a2.id as movie_id,
         a2.movie_title as movie_title,
         0 as free_cnt,
         COUNT(*) AS plus_cnt,
         0 as visitor_cnt
FROM     movie_request_2 a2
GROUP BY a2.movie_title

UNION ALL

SELECT   a3.id as movie_id,
         a3.movie_title as movie_title,
         0 as plus_cnt,
         0 as free_cnt,
         COUNT(*) AS visitor_cnt
FROM     movie_request_3 a3
GROUP BY a3.movie_title

Select COUNT in two table in one query with MYSQL

Here is one way:

select (select count(*) from table1) as t1_amount,
       (select count(*) from table2) as t2_amount

Here is another way:

select t1.t1_amount, t2.t2_amount
from (select count(*) as t1_amount from table1) t1 cross join
     (select count(*) as t2_amount from table2) t2

Your method does not work because the , in the from clause does a cross join. This does a cartesian product between the two tables.

Combine mysql count(*) for two different tables same column

I would use UNION ALL to combine the two comment tables in a common format, then do the JOIN:

SELECT P.*, TC.count
FROM (
    SELECT Ts.id_post, count(*) AS count
    FROM (
          SELECT id_post FROM Table1
          UNION ALL
          SELECT id_post FROM Table2
    ) AS Ts
    GROUP BY Ts.id_post
) AS TC
LEFT JOIN post AS P ON TC.id_post = P.id_post
GROUP BY P.id_post
ORDER BY TC.count DESC

MySQL SELECT COUNT(*) From two tables based on multiple conditions

This is you're probably looking for:

SELECT COUNT(C.id) AS [count]
FROM t_contacts C
INNER JOIN t_contacts_meta M ON M.cid = C.id
                               AND M.interest IN ('interest_1', 'interest_2', 'interest_n')
WHERE C.state = 'state_value'
    AND C.profession = 'profession_value'
    AND C.sex = 'sex_value'

Hope this will help you.

MySQL SELECT from two tables with COUNT

You could try this one:

SELECT COUNT(cus_order.order_id), cus.cust_id, cus.first_name, cus.last_name 
FROM cust_order cus_order, customer cus 
WHERE cus_order.cust_id = cus.cust_id
GROUP BY cust_id;

MySQL join and COUNT() on multiple tables

You will need to use DISTINCT, but also you need to count the IDs, not the foreign keys:

SELECT
    table1.name,
    COUNT(DISTINCT table2.id) AS table2_count,
    COUNT(DISTINCT table3.id) AS table3_count,
    COUNT(DISTINCT table4.id) AS table4_count,
    SUM(table4.size) AS table4_size
FROM table1
LEFT JOIN table2 ON table1.id = table2.table1_id
LEFT JOIN table3 ON table2.id = table3.table2_id
LEFT JOIN table4 ON table3.id = table4.table3_id
WHERE table1.id = 1

Here is a fiddle.

Explanation: The DISTINCT key word eliminates all duplicate values resulting in a list of unique values.

If you run your query without the COUNT() and SUM(), you get:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1024 
test  1          1          1          200 
test  1          (null)     (null)     (null) 
test  1          (null)     (null)     (null) 

So if you add the COUNT() and SUM(), you obviously get:

name  table1_id  table2_id  table3_id  size
test  4          2          2          1224 

However, using DISTINCT with your query won't help because you can clearly see the duplicate values, which will result in:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1224 

Now, if you run my query without the COUNT() and SUM(), you get:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1024 
test  1          1          2          200 
test  2          (null)     (null)     (null) 
test  3          (null)     (null)     (null) 

If you add the COUNT() and SUM(), you get exactly the same results like your query:

name  table1_id  table2_id  table3_id  size
test  4          2          2          1224 

However, because this time you have different values (i.e. not all are 1), so now if you count the unique values using DISTINCT, you get:

name  table1_id  table2_id  table3_id  size
test  3          1          2          1224 

Selecting Counts from Different Tables with a Subquery

This is a case where I would recommend correlated subqueries:

select a.userid, 
       (select count(*) from table1 t1 where t1.userid = a.userid) as cnt1,
       (select count(*) from table2 t2 where t2.userid = a.userid) as cnt2
from allusers a
where a.vip is not null;

The reason that I recommend this approach is because you are filtering the alllusers table. That means that the pre-aggregation approach may be doing additional, unnecessary work.

---

Related Topics