-1

I've a ResultSet of a Mysql Query something like this :- 

select concat(category_name,subcategory_name) from category

which is returning Results something like this :- -

MovieComedyMovies
CarsSportsCars
ShowsComedyShows

These 3 results are different tables itself.
I want to perform Count() on these tables which I'm getting in results that is- to check whether these tables contains any data or not.
P.S - I'm using MySQl 5.6

Sumit
  • 917
  • 1
  • 7
  • 18
  • Could you provide some sample data and expect result? – D-Shih Jul 06 '18 at 07:51
  • Suppose **MovieComedyMovies** table has 10 records & **CarsSportsCars** has 20 records. So what I'm looking for is to get **10** , **20** from my initial query. I think, I need to create some kind of Procedure to loop through each result and get count. – Sumit Jul 06 '18 at 07:56
  • Set up and use prepared statements. https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure – EzLo Jul 06 '18 at 09:58

2 Answers2

0

If I understand your comment correctly you can write a subquery on select clause, like this.

SELECT (SELECT COUNT(*) FROM MovieComedyMovies) AS MovieComedyMovies,
       (SELECT COUNT(*) FROM CarsSportsCars) AS CarsSportsCars

But from your question, you might want to do this.

CASE WHEN with SUM function.

select SUM(CASE WHEN concat(category_name,subcategory_name) = 'MovieComedyMovies' THEN 1 ELSE 0 END),
       SUM(CASE WHEN concat(category_name,subcategory_name) = 'CarsSportsCars' THEN 1 ELSE 0 END)
from category

Edit

From your comment I guessed you can try this.

select concat(category_name,subcategory_name)，count(*)  
from category
Group by concat(category_name,subcategory_name)
D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • Thanks @D-Shih , However What I'm looking for is - to get **count()** result of the tables which I'm getting from my --> **select concat(category_name,subcategory_name) from category** Query. So far what I've tried is -- **select count(*) from (select concat(category_name,subcategory_name) from category)** – Sumit Jul 06 '18 at 09:07
  • @sumit did you try my second query? I guess it will be work. Or you can put some sample data and expect result,. – D-Shih Jul 06 '18 at 09:26
  • The Problem here is I don't know how many results **select concat(category_name,subcategory_name) from category** going to return & the results keeps on Changing. Thats the reason it's quite difficult to implement second Query. – Sumit Jul 06 '18 at 09:42
  • I edit my answer you seem add `group by` and Alisa name can make your expect. – D-Shih Jul 06 '18 at 09:52
  • I'm getting an Error :-- **Unknown column 'grp' in 'field list**' – Sumit Jul 06 '18 at 09:59
  • I appreciate your efforts mate but seems today isn't quite a productive day for me. Anyways Thanks – Sumit Jul 06 '18 at 12:54
0

I'm copying your example query for historical purposes:

select concat(category_name,subcategory_name) from category

Assuming that's the query that still draws data:

SELECT count(Name) as Total, Name 
FROM (
       select concat(category_name,subcategory_name) as Name from category
     ) data
group by Name

Something along those lines. If you can build a fiddle with an MCVE you might get a more specific answer.

Héctor Álvarez
  • 494
  • 3
  • 18