group by question

angelk1983

Hello everyone, I have problems when doing GROUP by, I ask
select a, b, c, sum (num) group by a, b where c is a problem if you do n’t add the summary field, but adding it is time-consuming. What is a good solution? My field must be possible No more than three, select a, b, c, e, f, g, sum (num) group by a, b Do you have to add all the fields later?

prajna

select a, b, min (c), sum (num) group by a, b
See your specific needs

angelk1983

Do all fields have min added? I want this SQL to return the data in all the fields in my table! If you add all the fields after group by, it will take too much time!

fanfan1980

When specifying GROUP BY, all columns in any non-aggregate expression in the select list should be included in the GROUP BY list, or the GROUP BY expression must exactly match the select list expression.

恶魔右指

If column C is not added
Only specified aggregate functions (sum, min, max, count, avg, stdev ....)

angelk1983

If it is me, is it the only way to add all fields after group by?

prajna

You don't understand the meaning of grouping

angelk1983

Yes, I may not understand the concept of grouping, but I encountered this problem on the project, that is, when I bind the GRID, my GRID column must contain all the fields in the table, and the summary column is added, although I added all the fields after GROUP by, but the query speed is slow! But there are only two columns in my summary column!

prajna

How do the columns after the aggregation correspond to other columns? If the other columns are the same, add min or max.
In addition, increasing the number of fields in the query itself will affect efficiency. .

angelk1983

Thank youprajna, SELECT C_CODE, C_NAME, SUBSTRing (F_CODE, P_NSTYLSTAR, P_NSTYLLEN) FA_CODE, F_NAME
      FA_NAME, F_PRICE FA_PRICE, SUM (NUM_1) NUM_1, SUM (NUM_1 * F_PRICE)
      SUM_1 GROUP BY C_CODE, FA_CODE ORDER BY C_CODE, FA_CODE
     FROM PERCUR1
This is my source code, C_code is the store name code, C_NAME is the store name, FA_code is the clothing code, FA_NAME is the clothing name, FA_PRICE is the price, NUM_1 is the number of pieces, the latter column is the amount, I need so much data, but I must It is correct to add all the fields after GROUP by, and I only need to summarize C_code and FA_code. Is it necessary to add all the fields, or there are other good solutions!