?
Solved

mysql arithmetic with results of a subquery

Posted on 2009-07-08
8
Medium Priority
?
386 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
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…

762 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