[SOLVED] SQL Error: Operand should contain 1 column

SQL Error: Operand should contain 1 column

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?

Solution

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.