MySQL sum of two fields multiplied

i want to compute the total $ for records (rows) in a table where
each row has a quantity and a (different) unit price.

thus if the table contains:
(qty) (unit price)
1  10
2  100
3  1000
i want the answer =  3210

how can i do this without a while loop to multiply individually and accumulate with a running subtotal.
willsherwoodAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
select sum(qty * unit_price) as answer
from yourtable
0
 
willsherwoodAuthor Commented:
wow   it's that simple????
thanks
0
 
willsherwoodAuthor Commented:
hi angel,
i tried your solution (a few days ago) and it yields a huge number that is not correct.
i cannot figure out what it's multiplying and summing in order to yield such a large number.

it is in the context of other fields (unused in this computation), and has a WHERE clause on it, but that should filter as one would expect.
when i remove the SUM part, and change
SELECT SUM (qty * unit)  FROM ...
to
SELECT qty, unit FROM ...
mysql admin execute SQL shows me the correct rows, and i CSV captured them and took it into excel and made a third column for partial products, and the sum of the cells in that third column is correct

any thoughts of what i might be doing wrong?
i may just need to revert to doing a loop and manually accumulating...
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
unless there is a data type mismatch, or a join that makes too many results before the sum, the result has to be correct. without more information it will be impossible to tell.
0
 
willsherwoodAuthor Commented:
Thanks for the encouragement/follow-up.
your comment gave me the debugging confidence to lead to the following,  which might you have an opinion for why there would be a difference?  (i'm not understanding something basic about SQL queries)

SELECT SUM (qty * unit)  FROM A          (works)
SELECT SUM (qty * unit)  FROM A,B       (gives erroneous answer;    where B & its fields are not referenced; it was leftover from having copied/modeled this SUM query from another query)

I had thought that the FROM list ONLY gave a list of table that were going to be referenced and had no impact on operation,   sorta like a Global declaration in PHP and other languages.

let me know if i should make this a new question with points, i'm still somewhat new to all this.
0
 
willsherwoodAuthor Commented:
Clarification,  i see the "leak" now,  i need to figure out how to do a DISTINCT modifier for this.

(so nevermind;   still your comment led me to the right place!)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SELECT ... FROM A,B
will perform a cartesian product of the 2 tables, also know as CROSS JOIN.

run SELECT * FROM A,B to see what it returns, and hence what data it will sum up.
0
 
willsherwoodAuthor Commented:
(thanks for continuing to help me!)

here's the un-abstracted real statement i'm using, where i've whittled it down to deleting one thing and it works:

SELECT    SUM(BillableHours*SubContractorHourlyRate)    FROM tblChunk as CHU, tblCustomers as CUS where  CHU.SubID='2'

vs.

SELECT    SUM(BillableHours*SubContractorHourlyRate)    FROM tblChunk as CHU where  CHU.SubID='2'

when i do the following, it shows me the exact rows i want (one row exactly), which is why i'm confused for adding a table reference in FROM

SELECT      BillableHours, SubContractorHourlyRate      FROM tblChunk as CHU, tblCustomers as CUS where  CHU.SubID='2'

(and when i export the latter CSV results  to excel, it sums the products perfectly)

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>FROM tblChunk as CHU, tblCustomers as CUS where  CHU.SubID='2'
is there no link between the 2 tables?
0
 
willsherwoodAuthor Commented:
that's my quandry:  just the addition of the table in the FROM clause  causes this.
I'm no expert in SQL, but it does seem strange.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I'm no expert in SQL, but it does seem strange.
being expert in SQL, it is not strange to me, but expected.
see here the concepts of the joins explained:
http://en.wikipedia.org/wiki/Join_(SQL)

please note though that  MySQL also has the keyword CROSS JOIN, but it implemented it differently
0
 
willsherwoodAuthor Commented:
i need to re ask the question now more specifically, i'll post a new for-points question under MySQL
thanks again for all your help!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.