Results 1 to 7 of 7

Thread: UNION querry

  1. #1
    Join Date
    Aug 2015
    Location
    Bucharest
    Posts
    9

    Unanswered: UNION querry

    Hello there !
    My query is:
    Code:
    SELECT BRAND,BRAND_GROUP, SUB_BRAND ,SUM(INCOME) AS TOTAL_INCOME FROM "tema".MMT
    WHERE BRAND_GROUP IS NULL
    AND SUB_BRAND IS NULL
    GROUP BY BRAND,BRAND_GROUP,SUB_BRAND
    
    UNION
    
    SELECT BRAND,BRAND_GROUP, SUB_BRAND ,SUM(INCOME) AS TOTAL_INCOME FROM "tema".BGT
    WHERE BRAND_GROUP IS NULL
    AND SUB_BRAND IS NULL
    GROUP BY  BRAND,BRAND_GROUP,SUB_BRAND;
    and my output is :
    Code:
     BRAND BRAND_GROUP SUB_BRAND TOTAL_INCOME 
     ----- ----------- --------- ------------ 
     GBS   NULL        NULL             10000
     SWG   NULL        NULL             10000
     GBS   NULL        NULL             20000
     STG   NULL        NULL             20000
     GTS   NULL        NULL             30000

    The problem is that i have 2 categories of BRAND and I want to have just 1. Like this :

    Code:
    Brand		Brand_Group		Sub_brand		Total_Income
    GBS		-			-			30000
    STG		-			-			20000
    GTS		-			-			30000
    SWG		-			-			10000

    Can someone help me with an ideea?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do you need the union?
    ..or am I missing something
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2015
    Location
    Bucharest
    Posts
    9

    abc

    I figure it out how to do it:
    SELECT BRAND,BRAND_GROUP,SUB_BRAND,SUM(TOTAL_INCOME) AS TOTAL_INCOME FROM
    (SELECT BRAND,BRAND_GROUP, SUB_BRAND, SUM(INCOME) AS TOTAL_INCOME FROM "tema".MMT
    WHERE BRAND_GROUP IS NULL
    AND SUB_BRAND IS NULL
    GROUP BY BRAND,BRAND_GROUP,SUB_BRAND

    UNION

    SELECT BRAND,BRAND_GROUP, SUB_BRAND ,SUM(INCOME) AS TOTAL_INCOME FROM "tema".BGT
    WHERE BRAND_GROUP IS NULL
    AND SUB_BRAND IS NULL
    GROUP BY BRAND,BRAND_GROUP,SUB_BRAND)

    GROUP BY BRAND,BRAND_GROUP,SUB_BRAND;
    Last edited by iRelevant; 09-10-15 at 08:39.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are correct, but doing a bit of needless work. I find this easier to understand, and it will probably run faster too:
    Code:
    SELECT BRAND, BRAND_GROUP, SUB_BRAND
    ,  SUM(TOTAL_INCOME) AS TOTAL_INCOME
       FROM 
          (SELECT BRAND,BRAND_GROUP, SUB_BRAND, INCOME
             FROM "tema".MMT
             WHERE  BRAND_GROUP IS NULL
                AND SUB_BRAND IS NULL
          UNION ALL
          SELECT BRAND, BRAND_GROUP, SUB_BRAND,	INCOME
             FROM "tema".BGT
             WHERE  BRAND_GROUP IS NULL
                AND SUB_BRAND IS NULL
          ) AS A
       GROUP BY BRAND, BRAND_GROUP, SUB_BRAND;
    Give it a try and let us know how it works for you!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Just for my elightenment why is the Union needed.

    I would expect
    Code:
    SELECT Brand, BrandGroup, SubBrand, sum(Income) as TOTIncome
    FROM MyTable
    GROUP BY Brand, BrandGroup, SubBrand
    HAVING ISNULL(BrandGroup) and isNULL(SUBBRAND)
    ORDER BY sum(Income) DESC , Brand, BrandGroup, SubBrand
    to get there, without a UNION.

    granted Ive not run the query or used a large dataset but I would expect a single query without using the join to be there or thereabouts performance wise
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    He is grouping from two separate tables. That is why the UNION.

    Andy

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    fair enough, that'll teach me not to look at the detail
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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