SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UNION ALL doesn`t return all available data

    Hi , in below query I tried to joint some subqueries with "UNION ALL" operator to fetch some data in two columns (pack & warehouse) :
    Code MySQL:
    SELECT   ft.leafCode
            ,ft.leafName
            ,ft.colorsCode
            ,ft.pack
            ,ft.warehouse
    FROM (SELECT rs_leaves.leafCode
    		    ,rs_leaves.leafName
    		    ,rs_colors.colorsCode
    		    ,COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' THEN sent_receive_tbl.receivedNUM Else Null End), 0)
    		    - COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' THEN sent_receive_tbl.sentNum Else Null End), 0) As `pack`
            	,NULL AS `warehouse`
             	FROM sent_receive_tbl
            	INNER JOIN rs_leaves
            	ON sent_receive_tbl.leafID = rs_leaves.leafID
            	INNER JOIN rs_colors
            	ON sent_receive_tbl.colorsID = rs_colors.colorsID
            	Group By rs_leaves.leafCode, rs_leaves.leafName, rs_colors.colorsCode
    UNION ALL
           SELECT tt.leafCode
    		     ,tt.leafName
    		     ,tt.colorsCode
    		     ,NULL AS `pack`
             ,tt.warehouse
            	FROM (SELECT  st.leafCode
                            ,st.leafName
      		                  ,st.colorsCode
                            ,NULL AS `pack`
      		                  ,COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
                        FROM (SELECT  rs_leaves.leafCode
                               ,rs_leaves.leafName
        			                 ,rs_colors.colorsCode
                               ,NULL As `INPUT`
        			                 ,Sum(assign_details.assAmount) As `OUTPUT`
                               FROM assign_details
        			                 INNER JOIN rs_leaves
        			                 ON rs_leaves.leafID = assign_details.leafName
        			                 INNER JOIN rs_colors
        			                 ON rs_colors.colorsID = assign_details.leafColor
    			                     Group By rs_leaves.leafCode, rs_colors.colorsCode
    			                     UNION ALL
    			              SELECT  rs_leaves.leafCode
                                ,rs_leaves.leafName
                                ,rs_colors.colorsCode
                                ,Sum(receipt_details.recAmount) As `INPUT`
                                ,NULL As `OUTPUT`
        			                 FROM receipt_details
                               INNER JOIN rs_leaves
        			                 ON rs_leaves.leafID = receipt_details.leafName
        			                 INNER JOIN rs_colors
        			                 ON rs_colors.colorsID = receipt_details.leafColor
        			                 Group By rs_leaves.leafCode, rs_colors.colorsCode
                            ) As st
                            Group By st.leafCode,st.leafName,st.colorsCode
                   ) AS tt
           ) AS ft
          Group By ft.leafCode
          ,ft.leafName
          ,ft.colorsCode;
    In output I get NULL values as warehouse output but it is not valid :
    +----------+----------+------------+------+-----------+
    | leafCode | leafName | colorsCode | pack | warehouse |
    +----------+----------+------------+------+-----------+
    | A | Stone1 | 1014 | -5 | NULL |
    | B | Stone2 | 1020 | 50 | NULL |
    | B | Stone2 | 2000 | 345 | NULL |
    | C | Stone3 | 1032 | 68 | NULL |
    | F | Blur3 | 1014 | -6 | NULL |
    | I | Wood3 | 1032 | 215 | NULL |
    +----------+----------+------------+------+-----------+
    How can I take actual value for warehouse ?
    These are some descriptions to confirm that warehouse shouldn`t drive NULL value :
    mysql> select * from receipt_details;
    +-----------+-------+----------+----------+-----------+-----------+
    | detailsID | recID | leafName | leafCode | leafColor | recAmount |
    +-----------+-------+----------+----------+-----------+-----------+
    | 1 | 1 | 1 | A | 1 | 200 |
    | 2 | 1 | 2 | B | 3 | 112 |
    | 3 | 1 | 9 | I | 2 | 30 |
    | 4 | 2 | 9 | I | 2 | 36 |
    | 5 | 2 | 3 | C | 2 | 34 |
    | 6 | 6 | 2 | B | 5 | 8 |
    | 7 | 22 | 6 | F | 1 | 80 |
    +-----------+-------+----------+----------+-----------+-----------+
    mysql> select * from assign_details;
    +-----------+-------+-----------+----------+----------+-----------+-----------+
    | detailsID | assID | projectID | leafName | leafCode | leafColor | assAmount |
    +-----------+-------+-----------+----------+----------+-----------+-----------+
    | 1 | 1 | 1 | 1 | A | 1 | 20 |
    | 2 | 1 | 1 | 2 | B | 3 | 8 |
    | 3 | 2 | 2 | 3 | C | 2 | 4 |
    | 4 | 2 | 2 | 9 | I | 2 | 6 |
    | 5 | 2 | 1 | 6 | F | 1 | 30 |
    | 6 | 3 | 3 | 2 | B | 5 | 12 |
    | 7 | 3 | 1 | 2 | B | 3 | 5 |
    | 8 | 1 | 1 | 6 | F | 1 | 20 |
    | 9 | 3 | 1 | 1 | A | 1 | 15 |
    +-----------+-------+-----------+----------+----------+-----------+-----------+
    thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i don't think anything here is substantially different from the last time you posted this problem

    the debugging technique i would use is to start at the innermost subquery and run it completely by itself, verifying that it does produce the desired results

    then build up, one step at a time

    i am afraid that for anyone else who does not have access to your data -- or to an understanding of what you're doing -- this problem is too complex to give an answer on a forum like this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have just simplified it to be easy to analyze .
    Innermost subquery is working fine , I can gain warehouse values truly :
    +----------+----------+------------+------+-----------+
    | leafCode | leafName | colorsCode | pack | warehouse |
    +----------+----------+------------+------+-----------+
    | A | Stone1 | 1014 | NULL | 165 |
    | B | Stone2 | 1020 | NULL | -4 |
    | B | Stone2 | 2000 | NULL | 99 |
    | C | Stone3 | 1032 | NULL | 30 |
    | F | Blur3 | 1014 | NULL | 30 |
    | I | Wood3 | 1032 | NULL | 60 |
    +----------+----------+------------+------+-----------+
    So 50% is done correctly . Now I need to gain pack values simultaneously ;
    Like warehouse , pack is related to such data as leafCode , leafName , colorsCode ;
    So both columns are related to similar parameters and occasionally they are related to common data .
    i am afraid that for anyone else who does not have access to your data -- or to an understanding of what you're doing
    Maybe I`m going wrong !In simple description :
    I have 3 tabels : for example (t1 , t2 , t3) .
    warehouse is obtained from t1 and t2 ; pack is obtained from t3 ; now I need to join these tables to have warehouse and pack simultaneously ; Is the method I choosed to joining these tables correctly ? Is there any alternative solution for it ?


    In upper stage I gain correct output too :
    Code MySQL:
    SELECT rs_leaves.leafCode
                ,rs_leaves.leafName
                ,rs_colors.colorsCode
                ,COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' THEN sent_receive_tbl.receivedNUM ELSE NULL END), 0)
                - COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' THEN sent_receive_tbl.sentNum ELSE NULL END), 0) As `pack`
                ,NULL AS `warehouse`
                FROM sent_receive_tbl
                INNER JOIN rs_leaves
                ON sent_receive_tbl.leafID = rs_leaves.leafID
                INNER JOIN rs_colors
                ON sent_receive_tbl.colorsID = rs_colors.colorsID
                GROUP BY rs_leaves.leafCode, rs_leaves.leafName, rs_colors.colorsCode
    UNION ALL
    SELECT tt.leafCode
           ,tt.leafName
           ,tt.colorsCode
           ,NULL AS `pack`
           ,tt.warehouse
    FROM (SELECT  st.leafCode
                  ,st.leafName
                  ,st.colorsCode
                  ,NULL AS `pack`
                  ,COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
                  FROM (SELECT  rs_leaves.leafCode
                               ,rs_leaves.leafName
                               ,rs_colors.colorsCode
                               ,NULL As `INPUT`
                               ,Sum(assign_details.assAmount) As `OUTPUT`
                               FROM assign_details
                               INNER JOIN rs_leaves
                               ON rs_leaves.leafID = assign_details.leafName
                               INNER JOIN rs_colors
                               ON rs_colors.colorsID = assign_details.leafColor
                               GROUP BY rs_leaves.leafCode, rs_colors.colorsCode
                               UNION ALL
                         SELECT  rs_leaves.leafCode
                                ,rs_leaves.leafName
                                ,rs_colors.colorsCode
                                ,Sum(receipt_details.recAmount) As `INPUT`
                                ,NULL As `OUTPUT`
                                 FROM receipt_details
                                 INNER JOIN rs_leaves
                                 ON rs_leaves.leafID = receipt_details.leafName
                                 INNER JOIN rs_colors
                                 ON rs_colors.colorsID = receipt_details.leafColor
                                 GROUP BY rs_leaves.leafCode, rs_colors.colorsCode
                         ) As st
                         GROUP BY st.leafCode,st.leafName,st.colorsCode
         ) AS tt
    result :
    +----------+----------+------------+------+-----------+
    | leafCode | leafName | colorsCode | pack | warehouse |
    +----------+----------+------------+------+-----------+
    | A | Stone1 | 1014 | -5 | NULL |
    | B | Stone2 | 1020 | 50 | NULL |
    | B | Stone2 | 2000 | 345 | NULL |
    | C | Stone3 | 1032 | 68 | NULL |
    | F | Blur3 | 1014 | -6 | NULL |
    | I | Wood3 | 1032 | 215 | NULL |
    | A | Stone1 | 1014 | NULL | 165 |
    | B | Stone2 | 1020 | NULL | -4 |
    | B | Stone2 | 2000 | NULL | 99 |
    | C | Stone3 | 1032 | NULL | 30 |
    | F | Blur3 | 1014 | NULL | 30 |
    | I | Wood3 | 1032 | NULL | 60 |
    +----------+----------+------------+------+-----------+

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't understand your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sorry, i don't understand your data
    Ok , which data do you need ? or which lines should be described more?
    Could you please guide me about ambiguous information ?
    I tried to describe clearly what output I need , I hope my descriptions would be realizable for you because english is not my native language
    Ah ! this forum is my final trust to solve this problem .

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    well, we can start here --
    Code:
    SELECT rs_leaves.leafCode
         , rs_leaves.leafName
         , rs_colors.colorsCode
         , NULL As `INPUT`
         , SUM(assign_details.assAmount) AS `OUTPUT`
      FROM assign_details
    INNER
      JOIN rs_leaves
        ON rs_leaves.leafID = assign_details.leafName
    INNER
      JOIN rs_colors
        ON rs_colors.colorsID = assign_details.leafColor
    GROUP 
        BY rs_leaves.leafCode
         , rs_colors.colorsCode
    UNION ALL
    SELECT rs_leaves.leafCode
         , rs_leaves.leafName
         , rs_colors.colorsCode
         , SUM(receipt_details.recAmount) AS `INPUT`
         , NULL AS `OUTPUT`
      FROM receipt_details
    INNER
      JOIN rs_leaves
        ON rs_leaves.leafID = receipt_details.leafName
    INNER
      JOIN rs_colors
        ON rs_colors.colorsID = receipt_details.leafColor
    GROUP 
        BY rs_leaves.leafCode
         , rs_colors.colorsCode
    what does this produce and is it correct?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Above query displays INPUT and OUTPUT quantities of warehouse . In upper step this formula is used to calculate current inventory of warehouse :
    ,COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
    Above query result is correct :
    +----------+----------+------------+-------+--------+
    | leafCode | leafName | colorsCode | INPUT | OUTPUT |
    +----------+----------+------------+-------+--------+
    | A | Stone1 | 1014 | NULL | 35 |
    | B | Stone2 | 1020 | NULL | 12 |
    | B | Stone2 | 2000 | NULL | 13 |
    | C | Stone3 | 1032 | NULL | 4 |
    | F | Blur3 | 1014 | NULL | 50 |
    | I | Wood3 | 1032 | NULL | 6 |
    | A | Stone1 | 1014 | 200 | NULL |
    | B | Stone2 | 1020 | 8 | NULL |
    | B | Stone2 | 2000 | 112 | NULL |
    | C | Stone3 | 1032 | 34 | NULL |
    | F | Blur3 | 1014 | 80 | NULL |
    | I | Wood3 | 1032 | 66 | NULL |
    +----------+----------+------------+-------+--------+
    Also in upper step , I mean this code :
    Code:
    SELECT  st.leafCode
                  ,st.leafName
                  ,st.colorsCode
                  ,NULL AS `pack`
                  ,COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
                  FROM (
            ---here is subquery---
                       ) As st
                         GROUP BY st.leafCode,st.leafName,st.colorsCode
    drived result is correct :
    +----------+----------+------------+------+-----------+
    | leafCode | leafName | colorsCode | pack | warehouse |
    +----------+----------+------------+------+-----------+
    | A | Stone1 | 1014 | NULL | 165 |
    | B | Stone2 | 1020 | NULL | -4 |
    | B | Stone2 | 2000 | NULL | 99 |
    | C | Stone3 | 1032 | NULL | 30 |
    | F | Blur3 | 1014 | NULL | 30 |
    | I | Wood3 | 1032 | NULL | 60 |
    +----------+----------+------------+------+-----------+
    P.S : Red numbers tell the story .

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, so this part is correct, right?

    then which part is wrong?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes , that`s correct .
    In upper step drived result is correct too :
    Code:
    SELECT   tt.leafCode
                 ,tt.leafName
                 ,tt.colorsCode
                 ,NULL AS `pack`
                 ,tt.warehouse
                FROM (
          ---this is subquery---
                    ) AS tt;
    After using UNION ALL drived result is correct again :
    Code:
    SELECT rs_leaves.leafCode
                ,rs_leaves.leafName
                ,rs_colors.colorsCode
                ,COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' THEN sent_receive_tbl.receivedNUM ELSE NULL END), 0)
                - COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' THEN sent_receive_tbl.sentNum ELSE NULL END), 0) As `pack`
                ,NULL AS `warehouse`
                FROM sent_receive_tbl
                INNER JOIN rs_leaves
                ON sent_receive_tbl.leafID = rs_leaves.leafID
                INNER JOIN rs_colors
                ON sent_receive_tbl.colorsID = rs_colors.colorsID
                GROUP BY rs_leaves.leafCode, rs_leaves.leafName, rs_colors.colorsCode
    UNION ALL
           SELECT tt.leafCode
                 ,tt.leafName
                 ,tt.colorsCode
                 ,NULL AS `pack`
             ,tt.warehouse
                FROM (
          ---here is subquery---
                   ) AS tt;
    +----------+----------+------------+------+-----------+
    | leafCode | leafName | colorsCode | pack | warehouse |
    +----------+----------+------------+------+-----------+
    | A | Stone1 | 1014 | -5 | NULL |
    | B | Stone2 | 1020 | 50 | NULL |
    | B | Stone2 | 2000 | 345 | NULL |
    | C | Stone3 | 1032 | 68 | NULL |
    | F | Blur3 | 1014 | -6 | NULL |
    | I | Wood3 | 1032 | 215 | NULL |
    | A | Stone1 | 1014 | NULL | 165 |
    | B | Stone2 | 1020 | NULL | -4 |
    | B | Stone2 | 2000 | NULL | 99 |
    | C | Stone3 | 1032 | NULL | 30 |
    | F | Blur3 | 1014 | NULL | 30 |
    | I | Wood3 | 1032 | NULL | 60 |
    +----------+----------+------------+------+-----------+
    In above step I tried to fetch pack column values from sent_receive_tbl table .
    But in final step when I try to have warehouse and pack values simultaneously in one individual table with unique data (I mean every unique product has just one row in table) , the result will be wrong ! because I get NULL values for warehouse :
    Code:
    SELECT   ft.leafCode
            ,ft.leafName
            ,ft.colorsCode
            ,ft.pack
            ,ft.warehouse
    FROM (
          ---here are subqueries---
           ) AS ft
          GROUP BY ft.leafCode
          ,ft.leafName
          ,ft.colorsCode;
    +----------+----------+------------+------+-----------+
    | leafCode | leafName | colorsCode | pack | warehouse |
    +----------+----------+------------+------+-----------+
    | A | Stone1 | 1014 | -5 | NULL |
    | B | Stone2 | 1020 | 50 | NULL |
    | B | Stone2 | 2000 | 345 | NULL |
    | C | Stone3 | 1032 | 68 | NULL |
    | F | Blur3 | 1014 | -6 | NULL |
    | I | Wood3 | 1032 | 215 | NULL |
    +----------+----------+------------+------+-----------+

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you lost me again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you please tell me in which section ? Is it required to place tables structures here ?
    My favorite result should be :
    +----------+----------+------------+------+-----------+
    | leafCode | leafName | colorsCode | pack | warehouse |
    +----------+----------+------------+------+-----------+
    | A | Stone1 | 1014 | -5 | 165 |
    | B | Stone2 | 1020 | 50 | -4 |
    | B | Stone2 | 2000 | 345 | 99 |
    | C | Stone3 | 1032 | 68 | 30 |
    | F | Blur3 | 1014 | -6 | 30 |
    | I | Wood3 | 1032 | 215 | 60 |
    +----------+----------+------------+------+-----------+
    But now as I described warehouse result for all records is NULL !

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    what does this produce --
    Code:
    SELECT rs_leaves.leafCode
         , rs_leaves.leafName
         , rs_colors.colorsCode
         , COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' 
                             THEN sent_receive_tbl.receivedNUM 
                             ELSE NULL END), 0) -
           COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' 
                             THEN sent_receive_tbl.sentNum 
                             ELSE NULL END), 0) As `pack`
         , NULL AS `warehouse`
      FROM sent_receive_tbl
     INNER
      JOIN rs_leaves
        ON sent_receive_tbl.leafID = rs_leaves.leafID
     INNER
      JOIN rs_colors
        ON sent_receive_tbl.colorsID = rs_colors.colorsID
    GROUP 
         BY rs_leaves.leafCode
          , rs_leaves.leafName
          , rs_colors.colorsCode
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok , I used above query to find availabe inventory into 7th workstation ; I name it pack because that station is (packing workstation) .
    sent_receive_tbl table structure is :
    +-----------------+-------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +-----------------+-------------+------+-----+---------+----------------+
    | srID | int(11) | NO | PRI | NULL | auto_increment |
    | checkNumber | varchar(20) | NO | | NULL | |
    | leafID | int(11) | NO | | NULL | |
    | colorsID | int(11) | YES | | NULL | |
    | stationID | int(11) | NO | | NULL | |
    | receivedNum | double | NO | | 0 | |
    | sentNum | double | NO | | 0 | |
    | transactionDate | datetime | YES | | NULL | |
    | transactionTime | time | NO | | NULL | |
    | srComment | text | YES | | NULL | |
    +-----------------+-------------+------+-----+---------+----------------+

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    and does the query in post #12 produce the correct results?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes , it produces the correct results .

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    could you show them please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For query in post #12 :
    +----------+----------+------------+------+-----------+
    | leafCode | leafName | colorsCode | pack | warehouse |
    +----------+----------+------------+------+-----------+
    | A | Stone1 | 1014 | -5 | NULL |
    | B | Stone2 | 1020 | 50 | NULL |
    | B | Stone2 | 2000 | 345 | NULL |
    | C | Stone3 | 1032 | 68 | NULL |
    | F | Blur3 | 1014 | -6 | NULL |
    | I | Wood3 | 1032 | 215 | NULL |
    +----------+----------+------------+------+-----------+

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, i see what you are doing wrong (finally)

    you have 3 columns in your outer GROUP BY clause, and 5 non-aggregate columns in your SELECT clause
    Code:
    SELECT   ft.leafCode
            ,ft.leafName
            ,ft.colorsCode
            ,ft.pack
            ,ft.warehouse
    FROM (
          ---here are subqueries---  
         ) AS ft
          GROUP BY ft.leafCode
          ,ft.leafName
          ,ft.colorsCode;
    please go back and re-read the documentation about hidden columns, and then see if you can identify which of these columns are hidden
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In documentation there is one sentence that is not clear to me :
    When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part.
    What`s the purpose of "same values" ? I mean "same values" in which aspects ?
    Could you please describe it with one simple example ?

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    here's a simple example

    the data produced by your UNION query consists of the following rows from the first SELECT
    Code:
    leafCode  leafName  colorsCode  pack  warehouse
       A      Stone1       1014     NULL     165
       B      Stone2       1020     NULL      -4
       B      Stone2       2000     NULL      99
       C      Stone3       1032     NULL      30
       F      Blur3        1014     NULL      30
       I      Wood3        1032     NULL      60
    as well as the following rows from the second SELECT
    Code:
    leafCode  leafName  colorsCode  pack  warehouse
       A      Stone1       1014       -5    NULL 
       B      Stone2       1020       50    NULL 
       B      Stone2       2000      345    NULL 
       C      Stone3       1032       68    NULL 
       F      Blur3        1014       -6    NULL 
       I      Wood3        1032      215    NULL
    now let's rearrange them and put them into groups
    Code:
    leafCode  leafName  colorsCode  pack  warehouse
       A      Stone1       1014       -5    NULL 
       A      Stone1       1014     NULL     165
    
       B      Stone2       1020       50    NULL 
       B      Stone2       1020     NULL      -4
    
       B      Stone2       2000      345    NULL 
       B      Stone2       2000     NULL      99
    
       C      Stone3       1032       68    NULL 
       C      Stone3       1032     NULL      30
    
       F      Blur3        1014       -6    NULL 
       F      Blur3        1014     NULL      30
    
       I      Wood3        1032      215    NULL
       I      Wood3        1032     NULL      60
    these are the groups as defined by the GROUP BY columns -- leafCode, leafName, colorsCode

    let's look within the first group
    Code:
    leafCode  leafName  colorsCode  pack  warehouse
       A      Stone1       1014       -5    NULL 
       A      Stone1       1014     NULL     165
    here you can see that the values of the pack and warehouse columns are ~not~ the "same values" for all rows in the group

    consequently mysql is free to pick whichever value it wants for thes

    the solution is to apply an aggregate function to these columns

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the solution is to apply an aggregate function to these columns
    I had a view on "(Aggregate) Functions" in MySQL documentation and regard to your hints I choosed SUM() function for aggregation and my deduction was drived from this assumption :
    Every ~NULL~ value in each group should be summed with its tantamount ~not NULL~ value .
    So I modified the query like below and of course the drived result is correct now
    Code:
    SELECT   ft.leafCode
            ,ft.leafName
            ,ft.colorsCode
            ,SUM(pack) AS `pack`
            ,SUM(warehouse) AS `warehouse`
    FROM (
            ---here are subqueries---
           ) AS ft
          GROUP BY ft.leafCode
          ,ft.leafName
          ,ft.colorsCode;
    Dear r937 , now final result is right but is my deduction right too ?

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes, except for one small detail -- NULLs are ignored by aggregate functions

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok , but now there is another question for me !
    In above sample , warehouse and pack values have "double" property , but what about those values that are not "double" or "decimal" !? Let`s assume there is one column that its values are "string" .
    Could you please guide me which (aggregate) functions could be used for those columns that have "string" values ? (regard to MySQL 5)

    I am grateful for your unrivaled help

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    use MAX() instead of SUM() -- works on pack and warehouse in your example, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •