Grouping by Acc No. and getting a total from another column 1

[jpor]

Hi,

Currently been asked to produce an Informix SQL report that groups by an account number and then gives a total against a YTD column. For example before the group:

Acc No YTD totals

103 113.74
103 119.95
103 399.85

This would then look like this when grouped and calculated:

103 633.54

I have tried using the sum and trying the report in a .ace format with let statements.

Does anyone have any ideas how I could accomplish this?

Thanks in advance.



( "To become Wise, first you must ask Questions&quot

 

[BigCalm]

Can you post your current sql? That might help.

I'd imagine something like this:

select account_no, sum(order_value)
from customers, orders
where [joins]
and [conditions]
group by 1
order by 1

 

[jpor]

Big Calm

Here is the SQL I am using:

select plsuppm.supplier, plsuppm.name,
ytd_sales,supplier_category,date(dated),vat_amount,currency_amount
from plsuppm, plinvm
where plsuppm.supplier=plinvm.supplier
and date(dated)>= "01/06/04"
and date(dated)<= "30/06/04"
group by plsuppm.supplier
order by plsuppm.supplier




( &quot;To become Wise, first you must ask Questions&quot

 

[jpor]

Also this is the full ACE report I have written.

database
data
end

define
variable start date
variable stop date
variable exvat1 decimal
variable exvat2 decimal
variable exvattot decimal
variable tot decimal
variable xvattot decimal
variable grandtot decimal

end

input
prompt for start using "Enter Start Date : "
prompt for stop using "Enter End Date : "
end
output
page length 64
top margin 0
bottom margin 0
left margin 0
-- report to pipe "lp -d$GENPRINTER"
report to "/tmp/nonstocksupplier.csv"
-- report to "pg"
end


select plsuppm.supplier, plsuppm.name,
ytd_sales,supplier_category,date(dated),vat_amount,currency_amount
from plsuppm, plinvm
where plsuppm.supplier=plinvm.supplier
and date(dated)>= $start
and date(dated)<= $stop
order by 1

end
format
page header
print "Category",",",
col 2, "Supplier",",",
col 3, "Name",",",
col 4, "From",",",
col 5, "To",",",
col 6, "YTD Total (exc. VAT)",",",
col 7, "YTD Total (inc. VAT)"

on every row

let exvat1 = "0.0"
let exvat2 = "0.0"
let exvattot = "0.0"
let tot = "0.0"
let xvattot = "0.0"
let grandtot = "0.0"
let exvat1 = currency_amount/117.5
let exvat2 = exvat1*100
let exvattot = exvat2

-- after group of 1
let grandtot=grandtot+currency_amount
let xvattot=xvattot+exvat2

print supplier_category,",",
col 2,supplier,",",
col 3,name ,",",
col 4,start ,",",
col 5,stop , ",",
col 6, xvattot using "########.##",",",
col 7, grandtot using "########.##"

end

BAsically it asks the user for a start and end date works out V.A.T and produces the correct decimal places.


( &quot;To become Wise, first you must ask Questions&quot

 

[jpor]

Big Calm.
Tried your recommendation, but still get an individual list of suppliers and no sum of the YTD.


( &quot;To become Wise, first you must ask Questions&quot

 

[BigCalm]

I see you're using a tetra/sage system (on C-ISAM version?):

select plsuppm.supplier, plsuppm.name, ytd_sales,supplier_category,sum(vat_amoun
t) va,sum(currency_amount) ca
from plsuppm, plinvm
where plsuppm.supplier=plinvm.supplier
and date(dated)>= "01/06/04"
and date(dated)<= "30/06/04"
group by 1,2,3,4
order by 1

-- will probably do the job for you (you might want to use different aliases for the sums)

If you're outputting to a .csv then you'll need 'first page header' and not 'page header', and set your margins to zero.

 

[jpor]

Thanks Bigcalm. Indeed we are using Sage/TETRA. Currently using CS/3 ver 3.1. And Informix 7 SE on 7.30.U6

Will give your amendment a go.


( &quot;To become Wise, first you must ask Questions&quot

 

[jpor]

Bigcalm. The amendment worked like a charm. Thanks again.


( &quot;To become Wise, first you must ask Questions&quot