Can't get head round mysql subquery

Question

Im having trouble getting my head round subqueries in Mysql. Fairly simple ones are ok, and most tutorials I find rarely go beyond the typical:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

What I am trying to pull out of my database is the following (I'll try my best to explain this without any background on our db):

Retrieve list of customers belonging to particular rep and total amount spent in last month (in one column) and amount spent in month to date, in other column.

As results, this would look roughly as follows:

ID | NAME   | PREV MONTH | CUR MONTH
1  | foobar | £2300      | £1200
2  | barfoo | £1240      | £500

Query I am using to get the first part of the data is the following:

SELECT c.id,c.name, SUM(co.invoicetotal) as total
FROM customers as c
JOIN customerorders as co on co.customer_id = c.id
WHERE c.salesrep_id = 24
AND co.orderdate BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
GROUP by c.id
order by total desc

The DATE_SUB can be replaced by actual dates, as php variables will be going here eventually. As an example this just gives me valid data.

This gives me, for example:

ID | NAME   | TOTAL 
1  | foobar | £2300      
2  | barfoo | £1240   

So, ideally, my subquery would be this exact same query, but with the dates changed. I keep getting a #1242 - Subquery returns more than 1 row error.

Any suggestions or advice please?

Thanks in advance. Rob

Answer 1

The reason you get the error is because:

WHERE column1 = (SELECT column1 FROM t2)

t2.column1 is returning more than one result, but because of the equals operator before the subquery - only one value can be accepted.

So you either need to change it to IN:

WHERE column1 IN (SELECT column1 FROM t2)

...to accept multiple values. Or change the subquery to only return one variable - this example returns the highest t2.column1 value for the entire table:

WHERE column1 = (SELECT MAX(column1) FROM t2)

It all depends on what data you are trying to get.

Answer 2

SELECT  c.id, c.name,
        (
        SELECT  SUM(co.invoicetotal)
        FROM    customerorders co
        WHERE   co.customer_id = c.id
                AND co.orderdate BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
        ) AS prev_month,
        (
        SELECT  SUM(co.invoicetotal)
        FROM    customerorders co
        WHERE   co.customer_id = c.id
                AND co.orderdate BETWEEN CURDATE() AND CURDATE() + INTERVAL 1 MONTHS
        ) AS cur_month,
FROM    customers as c
WHERE   c.salesrep_id = 24
ORDER BY
        prev_month DESC

Answer 3

OMG Ponies is correct about why you got that error. Subqueries that are use in a comparison must always return a single value.

My guess is that you need to create two subqueries (one for prev and one for curr) and join them by user ID. Something like this:

SELECT prev.id,prev.name, prev.total, curr.total
FROM
(
SELECT c.id,c.name, SUM(co.invoicetotal) as total  
FROM customers as c JOIN customerorders as co on co.customer_id = c.id  
WHERE c.salesrep_id = 24  
AND co.orderdate BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()  
GROUP by c.id ORDER BY total desc  
) as prev
JOIN
(
SELECT c.id,c.name, SUM(co.invoicetotal) as total  
FROM customers as c JOIN customerorders as co on co.customer_id = c.id  
WHERE c.salesrep_id = 24  
AND co.orderdate > CURDATE()
GROUP by c.id ORDER BY total desc  
) as curr
ON prev.id=curr.id

Answer 4

I am leaving out the date calculations since you are generating that from code:

SELECT c.id,c.name, 
    SUM(case when co.orderdate >= @LastMonthStartDate and co.orderdate < @CurrentMonthStartDate then co.invoicetotal else 0 end) as LastMonthTotal,
    SUM(case when co.orderdate between @CurrentMonthStartDate and CURDATE() then co.invoicetotal else 0 end) as CurrentMonthTotalToDate
FROM customers as c 
JOIN customerorders as co on co.customer_id = c.id 
WHERE c.salesrep_id = 24 
   AND co.orderdate BETWEEN @LastMonthStartDate AND CURDATE() --remove this if you want customers that did not order in the last 2 months
GROUP by c.id 
order by total desc 

Answer 5

I don't think you actually need a subquery here (although you could do it that way if you wanted, I suppose); you're trying to do a sum on one group of records, and then another sum on another group of records (from the same table as the first group). The easiest way is to join to the table twice:

SELECT c.id,c.name, SUM(co_current.invoicetotal) as current_total,
     SUM(co_previous.invoicetotal) as previous_total
FROM customers as c
JOIN customerorders as co_current on (co_current.customer_id = c.id
     AND co_current.orderdate BETWEEN @CurrentMonthStartDate AND CURDATE())
JOIN customerorders as co_previous on (co_previous.customer_id = c.id
     AND co_previous.orderdate BETWEEN @PreviousMonthStartDate AND CURDATE())
WHERE c.salesrep_id = 24
GROUP by c.id
order by total desc

Answer 6

I agree with JacobM, but came up with a slightly different approach:

SELECT
    c.id,
    c.name, 
    SUM(co1.invoicetotal) as PREV_MONTH, 
    SUM(co2.invoicetotal) as CUR_MONTH, 
FROM
    customers as c, 
    customerorders as co1, 
    customerorders as co2
WHERE 
    c.salesrep_id = 24
    and  co1.customer_id = c.id
    and  co2.customer_id = c.id
    AND co1.orderdate BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
    AND co2.orderdate > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP by c.id
order by total desc

Not sure which would be more efficient.