How to do GROUP BY in UNION ALL?

Asked

Viewed 1,327 times

2

I have the following code:

String select1 = @"SELECT CODIGOB, DESCRICAO, QUANTIDADE FROM " + path + @"\IVENDA.DBF WHERE DATA BETWEEN {^" + datainicial + "} AND {^" + datafinal + @"} ";

String select2 = @"SELECT CODIGOB, DESCRICAO, QUANTIDADE FROM " + path + @"\NFCEI.DBF WHERE DATAE BETWEEN {^" + datainicial + "} AND {^ " + datafinal + @"} ";

String instrucao1 = @"SELECT CODIGOB, DESCRICAO, QUANTIDADE FROM (" + select1 + ")VND " + @"UNION All " + select2 + " ";

String sql= @"SELECT CODIGOB, DESCRICAO, QUANTIDADE FROM (" + instrucao1 + ")SQL GROUP BY CODIGOB, DESCRICAO ORDER BY QUANTIDADE DESC";

When doing instruction 1 it does not let me use GROUP BY. Would anyone know why ? How should I use GROUP BY when I have UNION ALL?

sql database

  • You’ve noticed you’re wearing select1 as subquery, however select2 is being used directly? If you use select2 as subquery also, I believe your problems are solved

  • You must first do the union with all the data and group afterwards. If necessary, make a select into in a temporary table with all the required fields, then do the group by from that table

  • Always write the query in an sql browser before trying to put it in a program that speaks to DBMS. Helps catch errors a lot more

  • 1

    @Ricardopoint, in this case, Cte is most appropriate for this use https://www.mysql.com/why-mysql/presentations/mysql-80-common-table-expressions/

  • 1

    Your problem is not in GROUP BY and yes, in the ORDER BY within the sub-consultations. Move it to the final query and test again.

  • 1

    It is also an option @Jeffersonquesado, only remains to know if the doubt is about MySql :-) Anyway has implementations to other databases, is a good suggestion.

  • 1

    Building the query in SQLServer in the same way as he created it, it is possible, but not when using the order by internally. Just as it is not possible in VIEWS. The ORDER BY clause is invalid in common displays, embedded functions, derived tables, sub-consultations and table expressions, unless TOP, OFFSET or FOR XML is also specified.

  • @Ricardopunctual, you’re right. I thought I read the Mysql tag. It follows generically : https://en.m.wikipedia.org/wiki/Common_table_expression

  • @But I have no problem with ORDER BY, even removed from the query but what is wrong with the group by

  • Now that I saw in your first query that you are using the wrong field on group by. The column calls DESCPROD and in the group by you used description. You’ve already corrected that?

  • @Smael I made change as you said. Give a look the column 'DESCPROD' is correct. What happens now for sure but it does not group the items that have different amount. What if I take the amount of error in sql query.

  • @Fabioc Your last appointment should not be: String sql= @"SELECT DESCRICAO, sum(QUANTIDADE) FROM (" + instrucao1 + ")SQL GROUP BY DESCRICAO ORDER BY QUANTIDADE DESC"; I didn’t understand why you took out the SUM.

Show 7 more comments

1 answer

4


To perform the necessary grouping, you will need to use the aggregation function SUM. And clear the field amount of group by.

String sql= @"SELECT DESCRICAO, SUM(QUANTIDADE) FROM (" + instrucao1 + ")SQL GROUP BY DESCRICAO ORDER BY QUANTIDADE DESC";

  • I swear I made it wrong, I put another field was 'CODE' worked.

Browser other questions tagged sql database

You are not signed in. Login or sign up in order to post.