mysql union and group by similar fields

Question

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.

I am getting the "Every derived table must have its own alias" error when attempting to execute. What am I typing in wrong?

I have researched this but couldn't find the answer. Every selected field has an alias? Or is the issue in the first SELECT?

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

Here is what I am attempting to do:

My original result:

Array
(
[0] => stdClass Object
    (
        [id] => 1
        [val] => 1
        [dat] => 2012-05-1
        [title] => Test Product
    )

[1] => stdClass Object
    (
        [id] => 1
        [val] => 8
        [dat] => 2012-06-1
        [title] => Test Product
    )

[2] => stdClass Object
    (
        [id] => 2
        [val] => 4
        [dat] => 2012-06-1
        [title] => Test Product 2
    )

[3] => stdClass Object
    (
        [id] => 3
        [val] => 6
        [dat] => 2012-06-1
        [title] => Test Product 3
    )

[4] => stdClass Object
    (
        [id] => 1
        [val] => 10
        [dat] => 2012-05-1
        [title] => Test Product
    )

[5] => stdClass Object
    (
        [id] => 1
        [val] => 8
        [dat] => 2012-06-1
        [title] => Test Product
    )

[6] => stdClass Object
    (
        [id] => 2
        [val] => 3
        [dat] => 2012-06-1
        [title] => Test Product 2
    )

[7] => stdClass Object
    (
        [id] => 3
        [val] => 3
        [dat] => 2012-06-1
        [title] => Test Product 3
    )

)

So if they have a similar date and ID, I need those to be just one result. Like so:

    Array
(
[0] => stdClass Object
    (
        [id] => 1
        [val] => 11
        [dat] => 2012-05-1
        [title] => Test Product
    )

[1] => stdClass Object
    (
        [id] => 1
        [val] => 8
        [dat] => 2012-06-1
        [title] => Test Product
    )

[2] => stdClass Object
    (
        [id] => 2
        [val] => 7
        [dat] => 2012-06-1
        [title] => Test Product 2
    )

[3] => stdClass Object
    (
        [id] => 3
        [val] => 9
        [dat] => 2012-06-1
        [title] => Test Product 3
    )

)

Please let me know if you need anything else. Thanks in advance.

nawfal · Accepted Answer · 2012-06-19T16:39:26.307

1

Try this:

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

                  ) AS t
                  GROUP BY id, dat
                  ORDER BY dat ASC

As the error suggests, every view/derived table must have an alias..

Edit: This will give you records with distinct id/dat pair. Seems this is what is you are after.

edited Jun 19 '12 at 16:39

answered Jun 19 '12 at 15:05

nawfal

70,104
56
326
368

I've seen that AS t in other examples, what key / purpose does that serve? – klye_g Jun 19 '12 at 15:07
Its an alias (meaning a name) you specify for every table in the query. For proper tables, it has a name of its own, but for derived tables, it doesn't have a name. Without a name it becomes impossible to specify the table. This is the standard behaviour of MySQL. Not so in SQLite. See this link for some more info.. http://stackoverflow.com/questions/9442119/what-does-t-refer-to-in-this-sql – nawfal Jun 19 '12 at 15:13
The results are not being grouped together now, as desired above. How would I restructure my given query to display as requested? – klye_g Jun 19 '12 at 15:31
@K_G I do not know what exactly you want. If you do not want duplicates in the resultant union of two queries, use `union` instead of `union all`. Moreover, you can also group on `t.dat` rather than doing two individual groups, but then it will behave differently. To know which behaviour u want, only u know. MySQL group by are a bit tricky to understand. Giving us some test data and output u expect would help us better in giving the right query – nawfal Jun 19 '12 at 15:41
what I am getting and what I want are already in the original question – klye_g Jun 19 '12 at 15:47

mysql union and group by similar fields

1 Answers1