sql query help

sql query

[andyp7]

I have managed to get myself totally confused.

I am trying to write a few pages to display sales totals against budgets from a mysql database.

The code that I have got to is giving explainable results but not the ones I need.

I am using 4 tables:
1.sales to hold sales info
2.budgets to hold monthly budgets
3.areas to hold the different revenue areas
4.clubs to hold the different sites info

The areas and clubs tables are joined with the sales table so that the report shows descriptions rather than numerical ids'.

The structure that I need the report to show is:
Club/Site¦¦Total Sales¦¦Total Budget¦¦Diff#¦¦Diff%¦¦Area

Please could someone point me in the right direction with my code below, Many thanks.

SELECT SUM( st_sales.s_val ) AS totalSales,
SUM( st_bud.b_amt) AS totalBud,
(SUM( st_sales.s_val ) - SUM( st_bud.b_amt )) AS difNo,
(( SUM(st_sales.s_val ) / SUM( st_bud.b_amt ) )*100 ) AS difPc, st_club.c_id, st_area.a_desc
FROM st_sales, st_bud
INNER JOIN st_club ON st_club.c_no = st_sales.s_club
INNER JOIN st_area ON st_area.a_id = st_sales.s_area
WHERE st_sales.s_club = st_bud.b_club AND st_sales.s_area = st_bud.b_area
GROUP BY st_area.a_desc, st_club.c_id
ORDER BY st_area.a_id DESC,difNo DESC, st_club.c_id;

[helenp]

Uups, I done several sums, but where is the problem in the joins or in the sums?

If you take away the joins, does the sums work?

[andyp7]

helenp,
Thanks for speed of reply, I've tried taking out the joins but I don't think that the problem lies there, the results I am getting seem to be doubling the entire total sales and returning this figure for each site and doing other wierd and wonderful maths giving totals that I can't explain.
I think that it might be something to do with the grouping?
Also this is only the simple stage, I will need to add where criteria for date ranges, sales people.

Andy