Databases
StackOverflow2013
Postsdbo
POmysql union and group by similar fields
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql union and group by similar fields
    text
    copied!<p>Here is my query (I replaced table names with generic ones). I am trying to do a union all on two different queries in order to group them all by date so that results with similar dates come out as one row. </p> <p>I am getting the "Every derived table must have its own alias" error when attempting to execute. What am I typing in wrong?</p> <p>I have researched this but couldn't find the answer. Every selected field has an alias? Or is the issue in the first SELECT?</p> <pre><code>SELECT SUM(val), id, dat, title FROM ( SELECT table1.product_id as id, SUM(table1.qty) as val, DATE_FORMAT(table1.created, '%Y-%m-1') as dat, table2.title as title FROM table1 LEFT JOIN table3 ON table1.event_id = table3.id LEFT JOIN table2 ON table1.product_id = table2.id WHERE table1.user_id = $user_id AND table3.active != 2 AND table3.temp = 0 AND table2.active != 2 GROUP BY dat UNION ALL SELECT table4.product_id as id, SUM(table4.qty) as val, DATE_FORMAT(table4.created, '%Y-%m-1') as dat, table2.title as title FROM table4 LEFT JOIN table5 ON table4.festival_id = table5.id LEFT JOIN table2 ON table4.product_id = table2.id WHERE table4.user_id = $user_id AND table5.active != 2 AND table2.active != 2 GROUP BY dat ) GROUP BY id ORDER BY dat ASC </code></pre> <p>Here is what I am attempting to do:</p> <p>My original result:</p> <pre><code>Array ( [0] =&gt; stdClass Object ( [id] =&gt; 1 [val] =&gt; 1 [dat] =&gt; 2012-05-1 [title] =&gt; Test Product ) [1] =&gt; stdClass Object ( [id] =&gt; 1 [val] =&gt; 8 [dat] =&gt; 2012-06-1 [title] =&gt; Test Product ) [2] =&gt; stdClass Object ( [id] =&gt; 2 [val] =&gt; 4 [dat] =&gt; 2012-06-1 [title] =&gt; Test Product 2 ) [3] =&gt; stdClass Object ( [id] =&gt; 3 [val] =&gt; 6 [dat] =&gt; 2012-06-1 [title] =&gt; Test Product 3 ) [4] =&gt; stdClass Object ( [id] =&gt; 1 [val] =&gt; 10 [dat] =&gt; 2012-05-1 [title] =&gt; Test Product ) [5] =&gt; stdClass Object ( [id] =&gt; 1 [val] =&gt; 8 [dat] =&gt; 2012-06-1 [title] =&gt; Test Product ) [6] =&gt; stdClass Object ( [id] =&gt; 2 [val] =&gt; 3 [dat] =&gt; 2012-06-1 [title] =&gt; Test Product 2 ) [7] =&gt; stdClass Object ( [id] =&gt; 3 [val] =&gt; 3 [dat] =&gt; 2012-06-1 [title] =&gt; Test Product 3 ) ) </code></pre> <p>So if they have a similar date and ID, I need those to be just one result. Like so:</p> <pre><code> Array ( [0] =&gt; stdClass Object ( [id] =&gt; 1 [val] =&gt; 11 [dat] =&gt; 2012-05-1 [title] =&gt; Test Product ) [1] =&gt; stdClass Object ( [id] =&gt; 1 [val] =&gt; 8 [dat] =&gt; 2012-06-1 [title] =&gt; Test Product ) [2] =&gt; stdClass Object ( [id] =&gt; 2 [val] =&gt; 7 [dat] =&gt; 2012-06-1 [title] =&gt; Test Product 2 ) [3] =&gt; stdClass Object ( [id] =&gt; 3 [val] =&gt; 9 [dat] =&gt; 2012-06-1 [title] =&gt; Test Product 3 ) ) </code></pre> <p>Please let me know if you need anything else. Thanks in advance.</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