How to Count Rows from two Tables with one Command in MySQL

Question

i want to count 2 tables from diferent table, im select the date then i group it.

here what i try

SELECT 
(SELECT date(date), COUNT(sh_sh) FROM sh_url GROUP BY date(date)) AS URLs, 
(SELECT date(date), COUNT(ip) FROM tracking GROUP BY date(date)) AS IP 
FROM dual

but i get error

1241 - Operand should contain 1 column(s)

is that posible to do it in one command?

the output should be like this

date(url)    count(sh_sh)    date(ip)    count(ip)
---------    ------------    ----------  ----------
2018-04-25   123             2018-04-25  123123
2018-04-26   456             2018-04-26  321
2018-04-27   789             2018-04-27  3125

You are creating a derived column from a subquery. When you do that, you can't stick 2 columns in 1 column. That's why it tells you that operand should contain 1 column. You can't select two columns (date and COUNT(ip)) into IP. Get it? You can rewrite this query using a JOIN. — N.B.
– N.B., Commented Apr 26, 2018 at 8:35

Tim Biegeleisen · Accepted Answer · 2018-04-26 08:37:16Z

2

I would phrase your problems using a join of two subqueries:

SELECT
    t1.date,
    t1.url_cnt,
    COALESCE(t2.ip_cnt, 0) AS ip_cnt
FROM
(
    SELECT date, COUNT(*) url_cnt
    FROM sh_url
    GROUP BY date
) t1
LEFT JOIN
(
    SELECT date, COUNT(*) ip_cnt
    FROM tracking
    GROUP BY date
) t2
    ON t1.date = t2.date;

answered Apr 26, 2018 at 8:37

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barmar · Accepted Answer · 2018-04-26 08:38:22Z

1

When you use a subquery as a value, it can only return one row and one column. You need to use a JOIN:

SELECT urls.date, urls.count AS sh_count, ip.count AS ip_count
FROM (SELECT date(date) AS date, COUNT(*) AS count FROM sh_url GROUP BY date(date)) AS urls
JOIN (SELECT date(date) AS date, COUNT(*) AS count FROM tracking GROUP BY date(date)) AS ip
ON urls.date = ip.date

answered Apr 26, 2018 at 8:38

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

How to Count Rows from two Tables with one Command in MySQL

1241 - Operand should contain 1 column(s)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1241 - Operand should contain 1 column(s)

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related