How to do GROUP BY in UNION ALL?

Question

How to do GROUP BY in UNION ALL?

Navigation

#1 by (3 votes)

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 statement 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

asked by anonymous 28.03.2017 / 13:32

1 answer

Compare system time with set time in a variable "repeat ... even cond." function in Python

score 3 · Accepted Answer

To perform the necessary grouping, you will have to use the SUM aggregation function. And remove the quantity field from group by .

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