CODEWITHSUNDEEP
mysqlsql
rodeokid
rodeokid

Reputation: 1

I scratch my head with UNION ALL

I am not an SQL query wizard at all, and here is my problem:

I have those 3 separate querys that works very well and each one gives me a nice looking frame with results on my website.

SELECT   arretsautressb AS Raison, SUM(minutesarrets) AS Minutes 
FROM     rapport_production_salles_blanches_2_repeat
GROUP BY Raison 
ORDER BY Minutes DESC

SELECT   redresseuseminutesarrets AS Raison, SUM(minutesarretsredresseuse) AS Minutes 
FROM     rapport_production_salles_blanches_3_repeat
GROUP BY Raison 
ORDER BY Minutes DESC

SELECT   raisonarretsconvoyeurair AS Raison, SUM(minutesarretsconvoyeurair) AS Minutes 
FROM     rapport_production_salles_blanches_4_repeat
GROUP BY Raison 
ORDER BY Minutes DESC

So everything is fine with those 3 results...the Raison column in my table return all the rows and the Minutes query SUM all rows Group by Raison... but i would like to merge those querys so it would give me only 1 big table with the results,instead on 3 tables.

But no matter how i try to format my UNION ALL code, what i get is 1 result only from each Raison query (so it takes only 1 row in sql table), instead of all the rows when they are separated. but the Minutes query is doing fine calculating the SUM of all the rows.

It would be cool if someone would just show me how to do it...cause i have been reading documentation for a couple of hours, and i am still stuck on this one.

This is what i tried so far, no error, but only 1 row of Raison is taken from sql table, instead of all rows:

SELECT * 
FROM ( (SELECT arretsautressb AS Raison, 
               SUM(minutesarrets) AS Minutes 
        FROM   rapport_production_salles_blanches_2_repeat t1)
      UNION ALL
       (SELECT redresseuseminutesarrets AS Raison,
               SUM(minutesarretsredresseuse) AS Minutes 
        FROM   rapport_production_salles_blanches_3_repeat t2)
      UNION ALL
       (SELECT  raisonarretsconvoyeurair AS Raison,
                SUM(minutesarretsconvoyeurair) AS Minutes 
        FROM    rapport_production_salles_blanches_4_repeat t3)
     ) AS t123
GROUP BY Raison 
ORDER BY Minutes DESC

This is what i get from my UNION ALL query:

UNION ALL

But this is what i get from 3 separated querys:

3 querys

Upvotes: 0

Views: 84

Answers (2)

fosjo
fosjo

Reputation: 27

I think your query doesn't return your desired result because of the following things:

  • It's fine to use a sub query where you specify the three tables and union them. However, you cannot use an aggregate (in this case SUM) without the use of GROUP BY.
  • Next, whenever you use GROUP BY, you should refer to the attribute instead of the column name. In my query I changed GROUP BY Raison to GROUP BY t1.arretsautressb.
  • I have used an ORDER BY on the outer query and I order by the second column, which is in this case the SUM(minutesarrets).

The query I would use is the following:

SELECT  *
FROM    (
            SELECT  arretsautressb AS Raison
            ,       SUM(minutesarrets) AS sum_minutes
            FROM    rapport_production_salles_blanches_2_repeat AS t1
            GROUP BY t1.arretsautressb
            UNION ALL
            SELECT  redresseuseminutesarrets AS Raison
            ,       SUM(minutesarretsredresseuse) AS sum_minutes
            FROM    rapport_production_salles_blanches_3_repeat AS t2
            GROUP BY t2.redresseuseminutesarrets
            UNION ALL
            SELECT  raisonarretsconvoyeurair AS Raison
            ,       SUM(minutesarretsconvoyeurair) AS sum_minutes
            FROM    rapport_production_salles_blanches_4_repeat AS t3
            GROUP BY t3.raisonarretsconvoyeurair
        ) AS t123
ORDER BY 2 DESC

Upvotes: 2

Rahul Patel
Rahul Patel

Reputation: 639

Try this: 

SELECT * FROM (
    SELECT * FROM (
        (SELECT arretsautressb AS Raison, SUM(minutesarrets) AS Minutes FROM rapport_production_salles_blanches_2_repeat t1) 
    UNION ALL 
    (SELECT redresseuseminutesarrets AS Raison, SUM(minutesarretsredresseuse) AS Minutes FROM rapport_production_salles_blanches_3_repeat t2)
) t1 
UNION All 
(SELECT raisonarretsconvoyeurair AS Raison, SUM(minutesarretsconvoyeurair) AS Minutes FROM rapport_production_salles_blanches_4_repeat t3) 
) AS t123 GROUP BY t123.Raison ORDER BY t123.Minutes DESC

Upvotes: 0

Related Questions