CODEWITHSUNDEEP
mysqlsql
user12285906
user12285906

Reputation: 83

count() in where clause from two tables

I am new to sql. I have two tables. I want to check the count of each tables, and then I want to select the number of counts when the counts of each tables are equal.

Here is my query;

$ratio = "SELECT count(*) AS ratio FROM live_agents,campaigns 
          WHERE live_agents.count(agent) = campaigns.count(id)";

NOTE : I know that the live_agents.count(agent) and campaigns.count(id) will not work. I am writing this line to clearly understand my situation. I want to count the number of rows of column agent in live_agents table and want to count the number of rows of column id in campaigns table.

Table live_agents:

agent
------
admin
user
staff

Table campaigns:

id
----
1
2

Expected output:

count
-------
2

Hope all of you understood my problem

Upvotes: 2

Views: 127

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

In MySQL, you can use LEAST():

SELECT LEAST( (SELECT Count(*) AS Ratio FROM live_agents),
              (SELECT Count(*) AS Ratio FROM campaigns)
            )

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try This

SELECT Min(Ratio) Count FROM
(
   SELECT Count(*) AS Ratio FROM live_agents
   UNION ALL
   SELECT Count(*) AS Ratio FROM campaigns
)X

Upvotes: 1

Related Questions