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'.

            

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

thx its working