CODEWITHSUNDEEP
sqlmysqlsubquerymysql-error-1242
prevailrob
prevailrob

Reputation: 275

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 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

Upvotes: 5

Views: 3526

Answers (5)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

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

Upvotes: 1

Mike C
Mike C

Reputation: 3117

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.

Upvotes: 0

Josh Yeager
Josh Yeager

Reputation: 3793

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

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425341

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

Upvotes: 5

OMG Ponies
OMG Ponies

Reputation: 332581

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.

Upvotes: 1

Related Questions