SQL Error: Operand should contain 1 column

Question

While working on a system I'm creating, I attempted to use the following query in my project:

SELECT WEEKLY_TOTALS.DOMAIN, WEEKLY_TOTALS.CATEGORY, DAY
    FROM( 
        (SELECT DOMAIN,CATEGORY,DAY,CNT FROM BASE_DATA) AS DAY_CNTS
    INNER JOIN
        (SELECT DOMAIN,CATEGORY,SUM(CNT) AS TOT FROM BASE_DATA
    GROUP BY DOMAIN,CATEGORY) AS WEEKLY_TOTALS
    ON (DAY_CNTS.DOMAIN = WEEKLY_TOTALS.DOMAIN, 
        DAY_CNTS.CATEGORY = WEEKLY_TOTALS.CATEGORY)
    )

That query though gives me an error: "#1241 - Operand should contain 1 column(s)

What stumps me is that I would think that this query would work no problem. Selecting columns, then selecting two more from another table, and continuing on from there. I just can't figure out what the problem is.

Is there a simple fix to this, or another way to write my query?

score 0 · Answer 1 · answered Jul 23 '14 at 10:33

You must use [DAY] instead of Day in your query:

SELECT WEEKLY_TOTALS.DOMAIN, WEEKLY_TOTALS.CATEGORY, [DAY]
    FROM( 
        (SELECT DOMAIN,CATEGORY,[DAY],CNT FROM BASE_DATA) AS DAY_CNTS
    INNER JOIN
        (SELECT DOMAIN,CATEGORY,SUM(CNT) AS TOT FROM BASE_DATA
    GROUP BY DOMAIN,CATEGORY) AS WEEKLY_TOTALS
    ON (DAY_CNTS.DOMAIN = WEEKLY_TOTALS.DOMAIN, 
        DAY_CNTS.CATEGORY = WEEKLY_TOTALS.CATEGORY)
    )

score 0 · Accepted Answer · edited May 03 '17 at 20:58

Your parentheses are off and you need table aliases for the subqueries and you need AND instead of a comma in the on clause.

In fact, you don't need the first subquery at all:

SELECT 
    WEEKLY_TOTALS.DOMAIN, WEEKLY_TOTALS.CATEGORY, DAY_CNTS.DAY
FROM 
    BASE_DATA DAY_CNTS 
INNER JOIN
    (SELECT 
         DOMAIN, CATEGORY, SUM(CNT) AS TOT
     FROM 
         BASE_DATA
     GROUP BY 
         DOMAIN, CATEGORY) WEEKLY_TOTALS ON DAY_CNTS.DOMAIN = WEEKLY_TOTALS.DOMAIN 
                                         AND DAY_CNTS.CATEGORY = WEEKLY_TOTALS.CATEGORY;

In addition, if this is on SQL Server, DAY is a reserved word so it should be appropriately escaped.

SQL Error: Operand should contain 1 column

2 Answers2