Solved

mysql arithmetic with results of a subquery

Posted on 2009-07-08
8
374 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
 
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now