homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Gold Sponsor 2015!
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Calculate sum of three columns (net sales, VAT, total sales
MySQL, MariaDB, sum, multiple columns

Msg#: 4422124 posted 2:42 am on Feb 27, 2012 (gmt 0)

How can the following constructed example be acheived:

I have a small table named Sales (and another named Purchases - but never mind that now ;) )

ID | ClientID | NetAmount | Date |
01 | 1000001 | 1987,50 | 010212 |
02 | 1000002 | 934,75 | 020212 |

I imagined I could then construct a query like:
select sum(NetAmount) as NetSale, format((NetSale/4),2) as VAT,(NetSale + VAT) as TotalSale
referencing to other results within the query,

Can it be done? That example did not work, obviously.



WebmasterWorld Senior Member eelixduppy us a WebmasterWorld Top Contributor of All Time 5+ Year Member

Msg#: 4422124 posted 6:50 pm on Feb 29, 2012 (gmt 0)

Perhaps something like this?

format((results.NetSale/4),2) VAT,
(results.NetSale*5/4) TotalSale
from (
select sum(NetAmount) NetSale from sales
) results

or you can just rewrite each result in terms of the sum:

select sum(NetAmount) as NetSale, format((sum(NetAmount)/4),2) as VAT,(sum(NetAmount)*5/4) as TotalSale

However, something like this should really be put into a stored procedure.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved