StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCan't get head round mysql subquery
text
Body
copied!<p>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:</p> <pre><code>SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); </code></pre> <p>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):</p> <p>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.</p> <p>As results, this would look roughly as follows:</p> <pre><code>ID | NAME | PREV MONTH | CUR MONTH 1 | foobar | £2300 | £1200 2 | barfoo | £1240 | £500 </code></pre> <p>Query I am using to get the first part of the data is the following:</p> <pre><code>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 </code></pre> <p>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.</p> <p>This gives me, for example:</p> <pre><code>ID | NAME | TOTAL 1 | foobar | £2300 2 | barfoo | £1240 </code></pre> <p>So, ideally, my subquery would be this exact same query, but with the dates changed. I keep getting a <code>#1242 - Subquery returns more than 1 row</code> error.</p> <p>Any suggestions or advice please?</p> <p>Thanks in advance. Rob</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload