Board index » delphi » GROUP question!
cOOl
Delphi Developer |
cOOl
Delphi Developer |
GROUP question!2004-04-08 08:48:33 PM delphi228 Hello all, I have table with 3 fields, contents of table look that: field_1 field_2 field_3 1 1 john 1 1 ivan 1 2 mark Can I group this rows and get this result field_1 field_2 field_3 2 2 john 1 2 mark Thank you. |
Soós Árpád
Delphi Developer |
2004-04-08 08:59:38 PM
Re:GROUP question!QuoteI have table with 3 fields, contents of table look that: That is possible but you need some field(s) that splits your records into groups. In your example you decided to place record 1&2 into group one and 3 into the second but how could the server (or anyone) find it out without further info? Field2 could play that role but you want to sum it too. |
cOOl
Delphi Developer |
2004-04-08 09:55:17 PM
Re:GROUP question!
Hi SoCs,
T_DEVNICI N_MESEC N_ST1 N_ST2 C_NAME 1 1,00 1,00 test 1 1,00 1,00 test2 SELECT N_MESEC, SUM(N_ST1) AS N_ST1, SUM(N_ST2) AS N_ST2, FROM T_DNEVNICI GROUP BY N_MESEC result of SELECT: T_DEVNICI N_MESEC N_ST1 N_ST2 1 2,00 2,00 ---------------------- but how to add column C_NAME, can I do that? tanks. |
Soós Árpád
Delphi Developer |
2004-04-08 10:23:17 PM
Re:GROUP question!Quotebut how to add column C_NAME, can I do that? SELECT N_MESEC, SUM(N_ST1) AS N_ST1, SUM(N_ST2) AS N_ST2, MIN(C_NAME) AS C_MIN_NAME FROM T_DNEVNICI GROUP BY N_MESEC This selects the first (in ASCII or collation order) name from each group. You can use MAX() as well. If you need to select a specific name other than the first or the last from each group, you probably need a stored procedure. Regards Árpád |
G. Allen Casteran
Delphi Developer |
2004-04-08 11:49:00 PM
Re:GROUP question!
In article <XXXX@XXXXX.COM>, XXXX@XXXXX.COM says...
Quote>but how to add column C_NAME, can I do that? N_MESEC, SUM(N_ST1) AS N_ST1, SUM(N_ST2) AS N_ST2, C_NAME FROM T_DNEVNICI GROUP BY N_MESEC, C_NAME They key is that the result columns containing static fields (not aggregate functions) must all appear in the group by clause. In your case if N_MESEC and C_NAME do not have a direct 1:1 relation then one of those columns SHOULD NOT be in the result set. Allen. |
cOOl
Delphi Developer |
2004-04-09 01:30:59 AM
Re:GROUP question!
Thank you SoCs!
Have a nice day! |