1

I've been doing this query 

SELECT
    A.*,
    (
        SELECT
            SUM(IF(LENGTH(B.picture) > 0, 1, 0)) AS A_picture_count,
            SUM(IF(LENGTH(B.video) > 0, 1, 0)) AS A_video_count
        FROM B
        WHERE B.A_id = A.id
    )
FROM A

which answers me with a Operand should contain 1 column(s) whereas 

SELECT
    A.*,
    (
        SELECT
            SUM(IF(LENGTH(B.picture) > 0, 1, 0)) AS A_picture_count
        FROM B
        WHERE B.A_id = A.id
    )
FROM A

works perfectly. Why MariaDB would not let me subquery more than 1 column ? Am I missing something?

As this question (MySQL - Operand should contain 1 column(s)) suggests,

You probably would answer that I should go this way

SELECT t1.*, sq.*
FROM table1 t1,
   (SELECT a,b,c FROM table2 ...) sq
WHERE ...

or using a join (but my query is much more complicated and my many SUMS and COUNTS mess a lot with my GROUP BY).

My question is more about "Why" than "How".

My final solution (which is not optimal):

SELECT
    A.*,
    (
        SELECT
            SUM(IF(LENGTH(B.picture) > 0, 1, 0)) AS A_picture_count,
        FROM B
        WHERE B.A_id = A.id
    )
    (
        SELECT
            SUM(IF(LENGTH(B.video) > 0, 1, 0)) AS A_video_count
        FROM B
        WHERE B.A_id = A.id
    )
FROM A
Community
  • 1
  • 1
MaximeBernard
  • 1,090
  • 1
  • 19
  • 33
  • 1
    Possible duplicate of [MySQL - Operand should contain 1 column(s)](http://stackoverflow.com/questions/14046838/mysql-operand-should-contain-1-columns) – stuartd May 10 '16 at 13:10
  • 1
    You can use sub-selects as joins, it's called `Common Table Expressions` – Stavr00 May 10 '16 at 13:11
  • Somehow kind of duplicate @stuartd indeed but I found out the problem myself. Just wondering why it cannot work this way. Unfortunately, the current answers from the other question does not answer the "Why", only the "How". – MaximeBernard May 10 '16 at 13:19
  • @MaximeBernard surely the why (from that question) is _"Your subquery is selecting two columns, while you are using it to project one column (as part of the outer SELECT clause)."_ – stuartd May 10 '16 at 13:23

2 Answers2

2

An outrageous hack would be to code both values into a single variable. Assuming that neither number could be more than 1000:

SELECT
    A.*,
    (
        SELECT
         1000 * SUM(IF(LENGTH(B.picture) > 0, 1, 0)) +
                SUM(IF(LENGTH(B.video) > 0, 1, 0)) AS A_pv_count
        FROM B
        WHERE B.A_id = A.id
    )
FROM A

You can get the components back using / 1000 and % 1000

SELECT floor(A_pv_count / 1000) AS A_picture_count,
            (A_pv_count % 1000) AS A_video_count
FROM
(SELECT
    A.*,
    (
        SELECT
         1000 * SUM(IF(LENGTH(B.picture) > 0, 1, 0)) +
                SUM(IF(LENGTH(B.video) > 0, 1, 0)) AS A_pv_count
        FROM B
        WHERE B.A_id = A.id
    )
 FROM A) AS AW
SQL Hacks
  • 1,322
  • 1
  • 10
  • 15
  • Ahah I like this smart solution. As you mentionned though, it's a bit outrageous and overkill. My primary goal was to simplify my query ;) – MaximeBernard May 10 '16 at 13:57
1

IMO No Database should let you select two columns in ( ) like that. It's clearly that you can not do select col1, (col2_ok), (multi_columns_HOW_ON_EARTH?)

One solution should be using tmp table like this (searched but MariaDB not support cte):

select A.*, tmp.A_picture_count, tmp.A_video_count
from A
inner join (SELECT A.id,
                SUM(IF(LENGTH(B.picture) > 0, 1, 0)) AS A_picture_count,
                SUM(IF(LENGTH(B.video) > 0, 1, 0)) AS A_video_count
            FROM A
            inner join B
            on B.A_id = A.id
            group by A.id
            ) tmp
on A.id = tmp.id;
Pham X. Bach
  • 5,284
  • 4
  • 28
  • 42
  • 1
    I see your point here. Thanks for the clarification. In my mind, it was more like a `SELECT col1, (col2_ok), (col3, col4)...` but I realize that it's kind of weird. – MaximeBernard May 10 '16 at 13:48