I am getting the error subquery must return only one column
when I try to run the following query:
SELECT mat.mat as mat1, sum(stx.total ) as sumtotal1,
(
SELECT mat.mat as mat, sum(stx.total) as sumtotal
FROM stx
LEFT JOIN mat ON stx.matid = mat.matid
LEFT JOIN sale ON stx.saleid = sale.id
WHERE stx.date BETWEEN '2013-05-01' AND '2013-08-31'
AND sale.userid LIKE 'A%'
GROUP BY mat.mat
) AS MyField
FROM stx
LEFT JOIN mat ON stx.matid = mat.matid
LEFT JOIN sale ON stx.saleid = sale.id
WHERE stx.date BETWEEN '2013-05-01' AND '2013-08-31'
AND sale.userid LIKE 'B%'
GROUP BY mat.mat
What is causing this error?
Put a subquery that returns multiple columns in the FROM
list and select from it.
A correlated subquery would be a bad idea to begin with. However, your query is not even correlated, but unrelated (no link to outer query) and seems to return multiple rows. This leads to (possibly very expensive and nonsensical) cross join producing a cartesian product, probably not your (secret) intention.
Looks like you really want:
SELECT m1.mat AS mat1, m1.sumtotal AS sumtotal1
,m2.mat AS mat2, m2.sumtotal AS sumtotal2
FROM (
SELECT mat.mat, sum(stx.total) AS sumtotal
FROM stx
LEFT JOIN mat ON mat.matid = stx.matid
LEFT JOIN sale ON stx.saleid = sale.id
WHERE stx.date BETWEEN '2013-05-01' AND '2013-08-31'
AND sale.userid LIKE 'A%'
GROUP BY mat.mat
) m1
JOIN (
SELECT mat.mat, sum(stx.total) AS sumtotal
FROM stx
LEFT JOIN mat ON mat.matid = stx.matid
LEFT JOIN sale ON sale.id = stx.saleid
WHERE stx.date BETWEEN '2013-05-01' AND '2013-08-31'
AND sale.userid LIKE 'b%'
GROUP BY mat.mat
) m2 USING (mat);
Both LEFT JOIN
are also pointless. The one on sale
is forced to a INNER JOIN
by the WHERE condition. The one on mat seems pointless, since you GROUP BY mat.mat
- except if you are interested in mat IS NULL
? (I doubt it.)
The case can probably be further simplified to:
SELECT m.mat
,sum(CASE WHEN s.userid LIKE 'A%' THEN x.total END) AS total_a
,sum(CASE WHEN s.userid LIKE 'B%' THEN x.total END) AS total_b
FROM sale s
JOIN stx x ON x.saleid = s.id
JOIN mat m ON m.matid = x.matid
WHERE (s.userid LIKE 'A%' OR s.userid LIKE 'B%')
AND x.date BETWEEN '2013-05-01' AND '2013-08-31'
GROUP BY 1;
The WHERE
condition can probably be simplified further, depending on your secret data types and indices. A boatload of information on precisely that case in this related answer on dba.SE.