Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL sum of two fields multiplied

Posted on 2007-11-16
12
Medium Priority
?
7,956 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
  • 7
  • 5
12 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

877 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