Can sql sum returned rows then work out % of total

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can sql sum returned rows then work out % of total

Author

Topic

Markie76
Starting Member

1 Post

Posted - 2010-05-19 : 11:03:03

Hi Guys,

I'm new to SQL programming (basic level) and I have come up against my first big challenge.

I am using a propriatetry product like MS reporting services to create an invoice.
Users enter an invoice into the system, specify the customer & invoice lines (each line can be tax exempt or assigned one of several tax codes).
Then they run the print process which queries the invoice header table and invoice detail table and retrieves all matching rows for that order/job number (created when you save your invoice entry).
The returned data is then inserted into the report template and produces a nice pdf invoice, showing customer details at the top, then listing the invoice lines showing tax amount, tax rate & net line total, at the bottom the tax is summed and an invoice total displayed.

Now, this is where my abilities end.

I now need to add a tax rate breakdown which breaks down the invoice by tax code and produces a percentage of the total attributable to that tax code. So that the customer can see how much they are spending in each tax bracket.

eg.

TC_____% of invoice total
0______0
1______27
2______64
3______9

My initial thinking was I could run two queries on the invoice detail table;
1. To return the total invoice amount
2. To return the total invoice amount grouped by tax code

Then to do a calculation which does 2/1 for each tax code returned.

But I can't figure out how to assign names to the grouped tax codes returned in the query AND then feed them into the calculation - if its possible to do with assigned field/column names.

So I would very much appreciate any help you can offer on this.

Is my thinking actually possible to do
OR
Is my approach incorrect.

Thanks,

Mark.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-19 : 11:32:30

Can you provide table structure, sample data and expected output.

Refer this on How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources