Hi, I made a report listing invoices that is working well. Here is the code:
SELECT 'dbr.sum', 'Sales','Cost','Freight','Rebate','Promo','Insurance','Profit';
SELECT 'dbr.avg', 'F%', 'R%','P%','I%','%';
SELECT 'dbr.avg_prefix', 'F%', '';
SELECT 'dbr.avg_prefix', 'R%', '';
SELECT 'dbr.avg_prefix', 'P%', '';
SELECT 'dbr.avg_prefix', 'I%', '';
SELECT 'dbr.avg_prefix', '%', '';
SELECT 'dbr.colstyle','Order', '[text-align:right]';
SELECT 'dbr.colstyle','Invoice', '[text-align:right]';
select c.Vendor_ID as 'Vendor',
c.Store as 'Store',
c.Client as 'Client',
c.Date as 'Date',
c.Invoice as 'Invoice',
c.Order as 'Order',
c.Sales as 'Sales',
c.Cost as 'Cost',
c.Freight as 'Freight',
c.Freight_Per as 'F%',
c.Rebate as 'Rebate',
c.Rebate_Per as 'R%',
c.Promo as 'Promo',
c.Promo_Per as 'P%',
c.Insurance as 'Insurance',
c.Insurance_Per as 'I%',
c.Profit as 'Profit',
c.Profit_Per as '%'
from t1.commission_report c
Group By c.Vendor_ID, c.Store,c.Client, c.Invoice,c.Order;
I tried to do a simplified version where you see the sum of all invoices per store. Here is my code:
SELECT 'dbr.hdr', 'Vendor';
SELECT 'dbr.sum', 'Sales','Cost','Freight','Rebate','Promo','Insurance','Profit';
SELECT 'dbr.avg', 'F%', 'R%','P%','I%','%';
SELECT 'dbr.avg_prefix', 'F%', '';
SELECT 'dbr.avg_prefix', 'R%', '';
SELECT 'dbr.avg_prefix', 'P%', '';
SELECT 'dbr.avg_prefix', 'I%', '';
SELECT 'dbr.avg_prefix', '%', '';
select c.Vendor_ID as 'Vendor',
c.Store as 'Store',
sum(c.Sales) as 'Sales',
sum(c.Cost) as 'Cost',
sum(c.Freight) as 'Freight',
avg(c.Freight_Per) as 'F%',
sum(c.Rebate) as 'Rebate',
avg(c.Rebate_Per) as 'R%',
sum(c.Promo) as 'Promo',
avg(c.Promo_Per) as 'P%',
sum(c.Insurance) as 'Insurance',
avg(c.Insurance_Per) as 'I%',
sum(c.Profit) as 'Profit',
avg(c.Profit_Per) as '%'
from t1.commission_report c
Group By c.Vendor_ID, c.Store;
My problem is that the total for the store and the main total are not calculated properly. Some invoices seem to be counted twice.
Can any see what's wrong with my code?
