[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

mysql arithmetic with results of a subquery

Posted on 2009-07-08
8
Medium Priority
?
387 Views
Last Modified: 2013-11-15
I have a value that I can only get using a subquery. I can't seem to use that value in arithmetic. For example,

(select sum(cost) from miscexpenses where accountid = property.id) as costs,

works just fine, and

sellingprice - proceeds as closing_costs,

works fine too, but when I try to use the subquery result below in arithmetic, it bombs out say "costs" does not exist.

costs + closing_costs as total_cost
0
Comment
Question by:MeridianManagement
[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
  • 2
8 Comments
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 24808623
Try

(select sum(cost) from miscexpenses where accountid = property.id) as costs, sellingprice - proceeds as closing_costs, sum(cost) + sellingprice - proceeds as total_cost

0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 24808762
I was simplifying the problem since it only pertained to this part but let me give you the actual chunk of the query.


// this works fine
 
 
(select sum(cost) from miscexpenses where accountid = property.id) as costs,
(select sum(cost) from deedfedex where accountid = property.id) as fedex,
(select sum(cost) from recordfedex where accountid = property.id) as record_fedex,
 
facamt + clerkfee - reimbursed + recordamt + notaryamt + deedcost + probateamt + probateamt2 + probateamt3 + probateamt4 + fedextaxcost + (sellingprice - proceeds) as other_costs,
 
 
 
 
// when I add sum(costs) or costs, it breaks and still says unknown column costs.
 
 
(select sum(cost) from miscexpenses where accountid = property.id) as costs,
(select sum(cost) from deedfedex where accountid = property.id) as fedex,
(select sum(cost) from recordfedex where accountid = property.id) as record_fedex,
 
facamt + clerkfee - reimbursed + recordamt + notaryamt + deedcost + probateamt + probateamt2 + probateamt3 + probateamt4 + fedextaxcost + (sellingprice - proceeds) + sum(costs) as other_costs,

Open in new window

0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 24809514
The point is, you cannot create a value in an "as" clause and then use it in another calculation.

Assume you want
a+b as z
c-d as y
y*z as w

You have to use all the individual components in the 'w' calculation
(a+b)*(c-d) as w
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:snoyes_jw
ID: 24809600
Move the subquery to the FROM clause. Not only will the field then be available, it will run much faster.

SELECT
  blah + c.costs + f.fedex AS other_costs
FROM
  property
  JOIN (select accountid, sum(cost) AS costs from miscexpenses GROUP BY accountid) AS c
    ON c.accountid = property.id
  JOIN (select accountid, sum(cost) AS fedex from deedfedex GROUP BY accountid) AS f
    ON f.accountid = property.id
0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 24816531
that is awesome, worked like a charm. I only have one tiny issue with your code. In some cases, there are no costs so it is returned as a NULL and sometimes, but not always, it will affect arithmetic to come out NULL too. How do I get rid of all the NULLS in the totals?
0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 24816535
fyi, I used a left join due to the fact that there were sometimes no entries in the other tables.
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 2000 total points
ID: 24816571
The COALESCE function will turn NULL into whatever value you like.
0
 
LVL 2

Author Closing Comment

by:MeridianManagement
ID: 31601332
awesome, the query worked perfect, but I don't think phpmyadmin likes our solution because it won't export it into a csv =(
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
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

656 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