none
UNION ERROR

    Question



  • declare @year varchar(4)

    set @year = '2010'

    SELECT TOP 10 



           count(Product_name) Sales,
           Product_Name
    FROM   

    PRODUCT as T 
        inner join 
        [dbo].[Master] as P 
        on T.ID = P.ID
        where  year(t.fill_date) =@year and Month(t.fill_date) =1
    GROUP BY product_name
    ORDER BY count(Product_name) DESC

    UNION ALL


    SELECT TOP 10 



           count(Product_name) Sales,
           Product_Name
    FROM   

    PRODUCT as T 
        inner join 
        [dbo].[Master] as P 
        on T.ID = P.ID
        where  year(t.fill_date) =@year and Month(t.fill_date) =2
    GROUP BY product_name
    ORDER BY count(Product_name) DESC

    GETTING ERROR 

    Msg 156, Level 15, State 1, Line 17
    Incorrect syntax near the keyword 'UNION'.

                
    Monday, January 07, 2013 3:56 PM

Answers

  • Instead of CTE use derived tables, e.g.

    SELECT * FROM
    
    (SELECT TOP 10
    
    
    
           count(Product_name) Sales,
           Product_Name
    FROM   
    
    PRODUCT as T 
        inner join 
        [dbo].[Master] as P 
        on T.ID = P.ID
        where  t.fill_date >=cast(@year as char(4)) + '0101' 
    AND t.fill_date < cast(@year as char(4)) + '0201' 
    GROUP BY product_name
    ORDER BY count(Product_name) DESC) Set1
    UNION ALL
    SELECT * FROM (
    
    SELECT TOP 10 
    
           count(Product_name) Sales,
           Product_Name
    FROM   
    
    PRODUCT as T 
        inner join 
        [dbo].[Master] as P 
        on T.ID = P.ID
        where   t.fill_date >=cast(@year as char(4)) + '0201' 
    AND t.fill_date < cast(@year as char(4)) + '0301' 
    GROUP BY product_name
    ORDER BY count(Product_name) DESC ) Set2
    
    ORDER BY Sales DESC


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi NModerator Monday, January 07, 2013 4:21 PM
    • Marked as answer by tsql_new Monday, January 07, 2013 4:52 PM
    Monday, January 07, 2013 4:20 PM
    Moderator

All replies

  • Try:

    ;with Set1 AS (SELECT TOP (10) 
    
    
    
           count(Product_name) Sales,
           Product_Name
    FROM   
    
    PRODUCT as T 
        inner join 
        [dbo].[Master] as P 
        on T.ID = P.ID
        where  t.fill_date >=cast(@year as char(4)) + '0101' 
    AND t.fill_date < cast(@year as char(4)) + '0201' 
    GROUP BY product_name
    ORDER BY count(Product_name) DESC),
    
    
    set2 AS (
    
    SELECT TOP (10) 
    
           count(Product_name) Sales,
           Product_Name
    FROM   
    
    PRODUCT as T 
        inner join 
        [dbo].[Master] as P 
        on T.ID = P.ID
        where   t.fill_date >=cast(@year as char(4)) + '0201' 
    AND t.fill_date < cast(@year as char(4)) + '0301' 
    GROUP BY product_name
    ORDER BY count(Product_name) DESC )
    
    select * from Set1
    UNION ALL
    select * from Set2

    Assuming that fill_date is a date or datetime column I changed the where expression into its sargable form.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, January 07, 2013 4:01 PM
    Moderator
  • When you do a UNION / UNION ALL, you cannot ORDER BY the Individual Queries. You can only give the ORDER BY 1 time at the last. If you still want to order by, I would suggest using a DERIVED table approach

    declare @year varchar(4)
    
     set @year = '2010'
    
    select	sales, product_name from (
    	 SELECT TOP 10 
    			count(Product_name) Sales,
    			Product_Name
    	 FROM   
    
    	 PRODUCT as T 
    		 inner join 
    		 [dbo].[Master] as P 
    		 on T.ID = P.ID
    		 where  year(t.fill_date) =@year and Month(t.fill_date) =1
    	 GROUP BY product_name
    	 ORDER BY count(Product_name) DESC
     ) t
    
     UNION ALL
    
     select	sales, product_name from (
    	SELECT TOP 10 
    			count(Product_name) Sales,
    			Product_Name
    	 FROM   
    
    	 PRODUCT as T 
    		 inner join 
    		 [dbo].[Master] as P 
    		 on T.ID = P.ID
    		 where  year(t.fill_date) =@year and Month(t.fill_date) =2
    	 GROUP BY product_name
    	 ORDER BY count(Product_name) DESC
    ) t1
    order by Sales DESC

    Monday, January 07, 2013 4:03 PM
  • It's due to the ORDER BY from the first query. Try -

    SELECT * FROM 
    (
    SELECT TOP 10 
           count(Product_name) Sales,
           Product_Name
    FROM   
    PRODUCT as T 
        inner join 
        [dbo].[Master] as P 
        on T.ID = P.ID
        where  year(t.fill_date) =@year and Month(t.fill_date) =1
    GROUP BY product_name
    --ORDER BY count(Product_name) DESC
    UNION ALL
    SELECT TOP 10 
           count(Product_name) Sales,
           Product_Name
    FROM   
    PRODUCT as T 
        inner join 
        [dbo].[Master] as P 
        on T.ID = P.ID
        where  year(t.fill_date) =@year and Month(t.fill_date) =2
    GROUP BY product_name
    --ORDER BY count(Product_name) DESC
    ) AS Sub
    ORDER BY Sub.Sales DESC


    Narsimha

    Monday, January 07, 2013 4:04 PM
  • but i want order by in each query ,coz its getting different result .

    also i cannot use cte as because i hv to run this in sql 2000

    anyother query

    Monday, January 07, 2013 4:18 PM
  • but i want order by in each query ,coz its getting different result .

    also i cannot use cte as because i hv to run this in sql 2000

    anyother query


    See the query I posted above with ORDER BY in both queries.
    Monday, January 07, 2013 4:19 PM
  • Instead of CTE use derived tables, e.g.

    SELECT * FROM
    
    (SELECT TOP 10
    
    
    
           count(Product_name) Sales,
           Product_Name
    FROM   
    
    PRODUCT as T 
        inner join 
        [dbo].[Master] as P 
        on T.ID = P.ID
        where  t.fill_date >=cast(@year as char(4)) + '0101' 
    AND t.fill_date < cast(@year as char(4)) + '0201' 
    GROUP BY product_name
    ORDER BY count(Product_name) DESC) Set1
    UNION ALL
    SELECT * FROM (
    
    SELECT TOP 10 
    
           count(Product_name) Sales,
           Product_Name
    FROM   
    
    PRODUCT as T 
        inner join 
        [dbo].[Master] as P 
        on T.ID = P.ID
        where   t.fill_date >=cast(@year as char(4)) + '0201' 
    AND t.fill_date < cast(@year as char(4)) + '0301' 
    GROUP BY product_name
    ORDER BY count(Product_name) DESC ) Set2
    
    ORDER BY Sales DESC


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi NModerator Monday, January 07, 2013 4:21 PM
    • Marked as answer by tsql_new Monday, January 07, 2013 4:52 PM
    Monday, January 07, 2013 4:20 PM
    Moderator
  • til getting same erro



    declare @year varchar(4)

    set @year = '2010'
    select * from(
    (SELECT TOP 10 




          
           count(Product_name) count,
           Product_Name
    FROM   

    [dbo].[product] as T 
        inner join 
        [dbo].[Master] as P 
        on T.id = P.id
        where  year(t.fill_date) =2010 and Month(t.fill_date) =1
    GROUP BY product_name

    ORDER BY count(Product_name) DESC) set1


    UNION ALL

    select  * from (
    SELECT TOP 10 


          
           count(Product_name) count,
           Product_Name
    FROM   

    [dbo].[product] as T 
        inner join 
        [dbo].[Master] as P 
        on T.id = P.id
        where  year(t.fill_date) =2010 and Month(t.fill_date) = 2
    GROUP BY product_name
    ORDER BY count(Product_name) DESC) set2

    order by count(Product_name) DESC

    Monday, January 07, 2013 4:30 PM
  • 1. You have an extra ( after select * in the first select

    2. Your last order by should be ORDER BY Sales DESC

    Please pay attention to what I wrote and what you posted. Also, your own WHERE condition is not sargable.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, January 07, 2013 4:33 PM
    Moderator
  • thx its working
    Monday, January 07, 2013 4:52 PM