Solved

MySQL sum of two fields multiplied

Posted on 2007-11-16
12
7,886 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 142

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
 
LVL 142

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 142

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 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Creating and Managing Databases with phpMyAdmin in cPanel.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now