CODEWITHSUNDEEP
mysqlcountsum
DEVOPS
DEVOPS

Reputation: 18790

How to take sum of two different query result counts in mysql?

I need to take total count of files from category + sub category + subsub category

For that I write this kind of a query using my views.

select ((select count(*) from view_category where 1=1)+ (select count(*) from view sub category where 1=1) + (select count(*) from view subsub category where 1=1)) as cnt

Its returning count value. But I want to know any other better method is available to get the same result.

I tried this way but its not working (How to SUM() multiple subquery rows in MySQL?)

select sum(int_val) from((select count(*) from view_category where 1=1) as int_val union (select count(*) from view sub category where 1=1) as int_val union (select count(*) from view subsub category where 1=1) as int_val ).

Upvotes: 1

Views: 2183

Answers (3)

DEVOPS
DEVOPS

Reputation: 18790

This is working for me

select count(*) from(
(select count(*) from view_category where 1=1) union (select count(*) from view sub category where 1=1) union (select count(*) from view subsub category where 1=1) ) AS int_val;

Upvotes: 0

DRapp
DRapp

Reputation: 48139

you don't need to do a union, and can just have each as its own from alias... As long as each query is returning only one row, you can do all sorts of crazy things. By ignoring any "join" condition, you get a Cartesian result, but a Cartesian of 1:1:1 will result with only 1 record

select
        ByCat.CatCount
      + BySubCat.SubCatCount
      + BySubSubCat.SubSubCatCount as Cnt
   from
      ( select count(*) CatCount
           from view_category ) ByCat,

      ( select count(*) SubCatCount
           from view_sub_category) BySubCat,

      (select count(*) SubSubCatCount
           from view_subsub_category ) BySubSubCat

Also imagine if you needed sum() or AVG() counts too from other elements... You could get those into a single row and use however you needed.

Upvotes: 1

kontur
kontur

Reputation: 5220

If the tables have similar structure, you might use UNION to unite the result and then perform one COUNT(*).

Upvotes: 0

Related Questions