Unable to get mysql header in header

Question

I am having trouble matching in Mysql. Pretty simple, all right, and most of the tutorials that I find rarely go beyond typical ones:

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

What I'm trying to pull from my database is the following (I will try to explain this without any prerequisites on our db):

Get a list of customers belonging to a certain reputation, and the total amount spent last month (in one column) and the amount spent during the month to date, in another column.

As a result, it looks something like this:

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

The query that I use to get the first piece of data is as follows:

 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 

DATE_SUB can be replaced with actual dates as php variables will be here in the end. As an example, this just gives me reliable data.

This gives me, for example:

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

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

Any suggestions or tips please?

Thanks in advance. Rob

Answer 1

 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 2

The reason you get the error is because:

 WHERE column1 = (SELECT column1 FROM t2) 

t2.column1 returns more than one result, but due to the equality operator, only one subquery can be accepted.

So, you need to either change it to IN:

 WHERE column1 IN (SELECT column1 FROM t2) 

... accept a few meanings. Or change the subquery to return only 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 3

I am leaving the date calculations since you are generating this from the 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 4

OMG Ponies correctly explains why you got this error. The subqueries that are used in the comparison must always return a single value.

I assume that you need to create two subqueries (one for prev and one for curr) and join them by user ID. Something like that:

 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 5

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 what will be more effective.