Two SQL Count() Queries

two SQL COUNT() queries?

One way is to join the table against itself:

select
   count(*) as TotalCount,
   count(s.id) as QualifiedCount
from
   MyTable a
left join
   MyTable s on s.id = a.id and {some conditions}

Another way is to use subqueries:

select
   (select count(*) from Mytable) as TotalCount,
   (select count(*) from Mytable where {some conditions}) as QualifiedCount

Or you can put the conditions in a case:

select
   count(*) as TotalCount,
   sum(case when {some conditions} then 1 else 0 end) as QualifiedCount
from
   MyTable

Related:

SQL Combining several SELECT results

Adding two count(*) results together

You could join the two queries according to the table1_id:

SELECT a.table1_id, a.cnt + b.cnt
FROM   (SELECT   table1_id, COUNT(*) AS cnt
        FROM     table2 
        GROUP BY table1_id) a
JOIN   (SELECT   table1_id, COUNT(*) AS cnt
        FROM     table3 
        GROUP BY table1_id) b ON a.table1_id = b.table1_id

Note:
This query implicitly assumes that both tables have the same table1_id values. If this is not the case, you'd need to use an outer join of some kind.

Calculate the difference between results of two count(*) queries based on 2 tables in PostgreSQL

Try this way:

select 
  (
    SELECT 
      "count"(*) as val1 
    from 
      tab1
  ) - (
    SELECT 
      "count"(*) as val2 
    from 
      tab2
  ) as total_count

Multiple COUNT() for multiple conditions in one query (MySQL)

SELECT color, COUNT(*) FROM t_table GROUP BY color

Select multiple count(*) in multiple tables with single query

A more traditional approach is to use "derived tables" (subqueries) so that the counts are performed before joins multiply the rows. Using left joins allows for all id's in basic to be returned by the query even if there are no related rows in either joined tables.

select
      basic.id
    , coalesce(a.LinkACount,0) LinkACount
    , coalesce(b.linkBCount,0) linkBCount
from basic 
left join (
     select id, Count(linkA_ID) LinkACount from LinkA group by id
     ) as a on a.id=basic.id
left join (
     select id, Count(linkB_ID) LinkBCount from LinkB group by id
     ) as b on b.id=basic.id

Count data from two columns in one or two queries

You cause aggregation and count() as follows:

select 
    jobid, 
    clientid, 
    count(*) cnt_clicks, 
    count(distinct url) cnt_distinct_url
from mytable
group by clientid, jobid

How to join two count queries together in one?

You could use a sub query that performs a union all, and then group and count:

select name, count(id) 
from (
        select reqname1 as name, entryid as id from table1
        union all
        select reqname2, comid from table2
     ) as combined
group by name

How do I add two count(*) results together on two different tables?

Wrap them up and use subqueries:

SELECT
(SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900)+
(SELECT COUNT(*) from Games WHERE little_kid1 = 900 
                              OR little_kid2 = 900 
                              OR little_kid3 = 900)
AS SumCount

Voila!

---

Related Topics