SmartestVEGA SmartestVEGA - 4 days ago 5
SQL Question

Group by while using union

I am trying to union all two queries, which one having a group by and another is just a hard-coded value without a table.

SELECT 'All' Category
UNION
SELECT Category as Category
FROM WV_BlogData
GROUP BY Category
ORDER BY COUNT(BlogDataID) desc


I am getting following error, how to get rid of this?


Msg 207, Level 16, State 1, Line 50 Invalid column name 'BlogDataID'.
Msg 104, Level 16, State 1, Line 50 ORDER BY items must appear in the
select list if the statement contains a UNION, INTERSECT or EXCEPT
operator.

Answer

Wrap your UNION up in a derived table:

select Category
from
(
    SELECT 'All' Category, 0 as cnt 
    UNION
    SELECT Category as Category, COUNT(BlogDataID) as cnt
    FROM WV_BlogData
    GROUP BY Category
) dt
ORDER BY cnt desc
Comments