group and order with union

Barbara 1 Reputation point
2021-10-18T20:19:14.657+00:00

I'm trying to join these two queries and I don't know why it doesn't work.

(select coluna1, coluna2, coluna3, coluna4,coluna5 as produto, sum(coluna6) coluna6, min(coluna7) as coluna7, max(coluna8) as coluna8, coluna9, coluna10
from view
where coluna5 in ('prod1',
'prod2',
'prod3')

and coluna1 is not null
group by coluna1, coluna2, coluna3, coluna4,coluna5, coluna9, coluna10
order by coluna1, coluna2, coluna4,coluna5, min(coluna7))

UNION

(select coluna1, coluna2, coluna3, coluna4,coluna5 as produto, max(coluna6) coluna6, min(coluna7nova) as coluna7, max(coluna8nova) as coluna8, coluna9, coluna10
from view
where coluna5 in ('prod4',
'prod5',
'prod6')
and coluna1 is not null
group by coluna1, coluna2, coluna3, coluna4,coluna5, coluna9, coluna10
order by coluna1, coluna2, coluna4,coluna5, min(coluna7nova))

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,675 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,243 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-10-18T21:44:22.223+00:00

    Next time you ask a question do not only say "does not work", but explain what you mean by that. Particularly, include any error message you get.

    As it happens, I can see the error directly this time. In an UNION query, the ORDER BY clause applies to the full UNION query; you cannot have ORDER BY clauses for each query. This might be query you want: 

    select coluna1, coluna2, coluna3, coluna4,coluna5 as produto, sum(coluna6) coluna6, min(coluna7) as coluna7, max(coluna8) as coluna8, coluna9, coluna10
from view
where coluna5 in ('prod1',
'prod2',
'prod3')
and coluna1 is not null
group by coluna1, coluna2, coluna3, coluna4,coluna5, coluna9, coluna10
UNION ALL
select coluna1, coluna2, coluna3, coluna4,coluna5 as produto, max(coluna6) coluna6, min(coluna7nova) as coluna7, max(coluna8nova) as coluna8, coluna9, coluna10
from view
where coluna5 in ('prod4',
'prod5',
'prod6')
and coluna1 is not null
group by coluna1, coluna2, coluna3, coluna4,coluna5, coluna9, coluna10
order by coluna1, coluna2, coluna4,coluna5, coluna7

    I changed UNION to UNION ALL, because that is what want most of the time. UNION remove duplicates which takes resources. And you may want to retain the duplicates.

    0 comments
  2. EchoLiu-MSFT 14,571 Reputation points
    2021-10-19T06:06:16.833+00:00

    Hi @Barbara ，

    Welcome to the microsoft tsql Q&A forum!

    Or: 

    ;With cte  
as(select coluna1, coluna2, coluna3,   
coluna4,coluna5 as produto,   
sum(coluna6) coluna6, min(coluna7) as coluna7,  
max(coluna8) as coluna8, coluna9, coluna10  
from view  
where coluna5 in ('prod1','prod2','prod3')  
and coluna1 is not null  
group by coluna1, coluna2, coluna3, coluna4,coluna5, coluna9, coluna10  
UNION  
select coluna1, coluna2, coluna3,  
coluna4,coluna5 as produto,   
max(coluna6) coluna6, min(coluna7nova) as coluna7,  
max(coluna8nova) as coluna8, coluna9, coluna10  
from view  
where coluna5 in ('prod4','prod5','prod6')  
and coluna1 is not null  
group by coluna1, coluna2, coluna3, coluna4,coluna5, coluna9, coluna10)  
  
select * from cte  
order by coluna1, coluna2, coluna4,coluna5,coluna7

    For details, please refer to:
    Using UNION of two SELECT statements with ORDER BY

    If you have any question, please feel free to let me know.

    Regards
    Echo

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments