Solved

mysql arithmetic with results of a subquery

Posted on 2009-07-08
8
377 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 27

Expert Comment

by:yodercm
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:yodercm
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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 500 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 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