Solved

MySQL sum of two fields multiplied

Posted on 2007-11-16
12
7,936 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:willsherwood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20301960
select sum(qty * unit_price) as answer
from yourtable
0
 

Author Comment

by:willsherwood
ID: 20301983
wow   it's that simple????
thanks
0
 

Author Comment

by:willsherwood
ID: 20309279
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20310436
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
 

Author Comment

by:willsherwood
ID: 20312062
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
 

Author Comment

by:willsherwood
ID: 20312074
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312084
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
 

Author Comment

by:willsherwood
ID: 20312133
(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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312232
>FROM tblChunk as CHU, tblCustomers as CUS where  CHU.SubID='2'
is there no link between the 2 tables?
0
 

Author Comment

by:willsherwood
ID: 20312313
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312493
>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
 

Author Comment

by:willsherwood
ID: 20312559
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question