Welcome to WebmasterWorld Guest from 54.166.130.147

Forum Moderators: open

Message Too Old, No Replies

Calculate sum of three columns (net sales, VAT, total sales

MySQL, MariaDB, sum, multiple columns

     
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.
6:50 pm on Feb 29, 2012 (gmt 0)

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



Perhaps something like this?


select
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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month