SQL statement ?!

[xxyy]

Is it possible using sql statement to get the following:
From:
----------------------------------------------------------
Fld1 fld2 fld3 fld4
----------------------------------------------------------
100 invoice
100 invoice
100 water 20.00
100 water 30.00
100 water2 15.00
100 water2 25.00
------------------------------------------
To:
----------------------------------------------------------
Fld1 fld2 fld3 fld4
----------------------------------------------------------
100 invoice
100 invoice
100 water 50.00
100 water2 40.00
------------------------------------------
Thanks in advance.

 

[MrDataGuy]

The 'Key' to doing it is to use the "Group By" and Sum Clause.

(Code not tested)

Select Fld1, Fld2, Fld3, Sum( Fld4) as Sum_Fld ;
Group by Fld1, Fld2, Fld3 ;
into cursor qResult

Note in this case the first 2 rows would be combined into one record.

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[xxyy]

I've already done this.
The problem is that, first two rows I don't want to be merged.

 

[Dan Freeman]

You may have to pre-seed the query source with values that won't group together on those first two rows, but will group together on the others. It's counter-intuitive as all get out, but you can't say "GROUP BY ALL ... EXCEPT".

I can't help thinking, though, that somewhere under the hood this is a crosstab waiting to happen. Is your "From" example here your actual source data?

 

[xxyy]

danfreeman,

Yes it is from actual source data, but it is simplified to less columns.

It seems that I've to manage this without using SQL statement.

Thanks,
xxyy

 

[Olaf Doschke]

You can do trickery. Also untested:

Select Fld1, Fld2, Fld3, Sum( Fld4) as Sum_Fld, iif(Fld1='invoice',SYS(2015),Space(10)) as groupbyexcept ;
Group by 1,2,3,5 ;
into cursor qResult

The iif expression could also be Recno()<3 for example, to keep the first 2 records out of grouping.

But on the other it seems you're mixing up head records with detail records in one table. You coud query the head records and union that result with the rest of the data. Or append the two query results via APPEND.

Bye, Olaf.

 

[Olaf Doschke]

Well, of course it should be iif(fld2='invoice'...)

Bye, Olaf.

 

[MrDataGuy]

OK add UNION to the mix
(Untested code)

Select Fld1, Fld2, Fld3, Sum_Fld ;
Where empty( Fld3 + fld4) ;
UNION ;
Select Fld1, Fld2, Fld3, Sum( Fld4) as Sum_Fld ;
Where not empty( Fld3 + fld4) ;
Group by Fld1, Fld2, Fld3 ;
into cursor qResult

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[xxyy]

Olaf and Antony,

Thank you for your replies.

Buy,
xxyy

 

[Olaf Doschke]

Anthony,

good, but it should be "Fld4 as Sum_Fld" in the first sql of course.

Bye, Olaf.