Can't get head round mysql subquery

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 col开发者_如何学JAVAumn.

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

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

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.

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 

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

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.