mysql arithmetic with results of a subquery

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
LVL 2
MeridianManagementAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cornelia YoderArtistCommented:
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
MeridianManagementAuthor Commented:
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
Cornelia YoderArtistCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

snoyes_jwCommented:
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
MeridianManagementAuthor Commented:
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
MeridianManagementAuthor Commented:
fyi, I used a left join due to the fact that there were sometimes no entries in the other tables.
0
snoyes_jwCommented:
The COALESCE function will turn NULL into whatever value you like.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MeridianManagementAuthor Commented:
awesome, the query worked perfect, but I don't think phpmyadmin likes our solution because it won't export it into a csv =(
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.