Board index » delphi » GROUP question!

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.
 
 

Re:GROUP question!

Quote
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
If I get it right you want to get the sum of field1 and field2 and the first
from field3.
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.
 

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.
 

Re:GROUP question!

Quote
but how to add column C_NAME, can I do that?
tanks.
If you want to include a specific name from each group, you have two options
using min() and max():
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
 

Re:GROUP question!

In article <XXXX@XXXXX.COM>, XXXX@XXXXX.COM says...
Quote
>but how to add column C_NAME, can I do that?
>tanks.

If you want to include a specific name from each group, you have two options
using min() and max():

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
If N_MSEC is a direct relation to C_NAME then you can
SELECT
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.
 

Re:GROUP question!

Thank you SoCs!
Have a nice day!