Solved

MySQL sum of two fields multiplied

Posted on 2007-11-16
12
7,924 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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
when to use sequences in mysql 4 43
Help needed with Powershell  XML to MySQL 5 88
Using cfstoredproc to return query data 2 69
How to use 2 ON statements in inner join 3 44
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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