Solved

MySQL SUM aggregate involving a sub-statement that pre selects the rows that are to be SUMmed

Posted on 2007-11-19
6
425 Views
Last Modified: 2012-06-27
I have a MySQL (5)  situation where i need to nest a selection "filtering" first and then perform a sum of products  (hours * rate) for those rows selected(filtered).

The problem i'm having if i don't "insulate" the selection, the implicit JOIN causes the SUM(hours*rate) to count the rows many times over.

What is the technique, within one overall query,  to  accomplish the following nesting:

SELECT  SUM(hours*rate)  FROM    
      (
        SELECT TableID  FROM  tbl1, tbl2  WHERE various conditions
      )

thanks


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
  • 4
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312652
what is the link between the 2 tables?
if there is "none", why do you query it "at the same time"?
0
 

Author Comment

by:willsherwood
ID: 20312798
I'm not sure how to express it:  the inner nested query returns the (distinct) keys for the table
that the top-level  sum of products then uses just those rows (once).

I'm hoping that a nested technique like this will "insulate" from all the extra SUMmation that it was doing as a flat query
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20313392
can you please show the table structure, and the foreign key definition, eventually some sample data?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:willsherwood
ID: 20313408
i hear your need, i'll sanitize for public posting-- will take a while
0
 

Author Comment

by:willsherwood
ID: 20316794
i was able to resolve it using a SUB QUERY  this isolated the selection of which rows i wanted to perform the operation on, and then i selected those rows  with   WHERE  ID =   (sub SELECT ID FROM  )  that returned a column of just the proper row IDs)


thanks

will
0
 

Author Closing Comment

by:willsherwood
ID: 31409940
your questions help me research the right answer, thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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 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://…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

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