How can I select from another selected query

Question

1.00/5 (1 vote)

See more:

I get an error "Invalid object name 'ttt'".
What's the correct query?

SQL

Select 
AgencyName,
PerName,
Branch,
(Select SUM(ttt.OfferCount) from ttt where ttt.Satate=5) as Done,
(Select SUM(ttt.OfferCount) from ttt where ttt.State!=5) as NotDone
from 
(select TB_Agency.AgencyName, TB_Personel.PerName, TB_Offer.State, count(TB_Offer.Branch) as OfferCount,
TB_Offer.State
from TB_Agency

join TB_Personel on TB_Agency.AgencyID= TB_Personel.AgencyID

join TB_Offer on TB_Personel.PerID = TB_Offer.PerID

GROUP BY TB_Acente.AgencyName, TB_Personel.PerName, TB_Offer.Branch,TB_Offer.State
) 
as
ttt

Posted 16-Sep-15 4:00am

amagitech

Updated 16-Sep-15 4:53am

v2

Add a Solution

Comments

FrankNight 17-Sep-15 3:29am

subqueries syntax is subject to change across database server. What DB Server are you using?

Try to run the subquery alone... I don't think it works as you expect:
TB_Offer.State is present twice... it's correct?!?
In the group by expression there is also the target of the aggregation function..

select
TB_Agency.AgencyName,
TB_Personel.PerName,
TB_Offer.State,
count(TB_Offer.Branch) as OfferCount,
//TB_Offer.State (?!?)
from
TB_Agency
join TB_Personel on TB_Agency.AgencyID= TB_Personel.AgencyID
join TB_Offer on TB_Personel.PerID = TB_Offer.PerID
GROUP BY
TB_Acente.AgencyName,
TB_Personel.PerName,
//TB_Offer.Branch, (?!?)
TB_Offer.State

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-09-16T04:18:00

Solution 1

You can't select from the results group!

Look at your code: it boils down to:

SQL

SELECT branch, (SELECT COUNT(*) FROM ttt) FROM MyTable AS ttt

So in order to select the second term, it must in each case evaluate all rows. But in order to evaluate all rows, it first needs to evaluate the second term.

I'm not sure exactly what results you want from your data, so I can;t make a concrete solution, but that certainly isn't it!

It's possible that you just need to move the "AS ttt" inside the bracket, but...

Posted 16-Sep-15 4:18am

OriginalGriff

Comments

amagitech 16-Sep-15 10:31am

Select
ttt.AgencyName,
ttt.PerName,
ttt.Branch,
from
(select TB_Agency.AgencyName, TB_Personel.PerName, TB_Offer.State, count(TB_Offer.Branch) as OfferCount,
TB_Offer.State
from TB_Agency

join TB_Personel on TB_Agency.AgencyID= TB_Personel.AgencyID

join TB_Offer on TB_Personel.PerID = TB_Offer.PerID

GROUP BY TB_Acente.AgencyName, TB_Personel.PerName, TB_Offer.Branch,TB_Offer.State
)
as
ttt

has worked I can İmagine difference between.
I want to do this.
http://i62.tinypic.com/1z3q9l1.jpg

Simon_Whale · Answer 2 · 2015-09-16T05:41:00

I would possibly look at modifying the code to the following

SQL

Select
AgencyName,
PerName,
Branch,
(SUM(case when ttt.State = 5 then ttt.OfferCount else 0 end) as Done,
(SUM(case when ttt.State != 5 then ttt.OfferCount else 0 end) as NotDone
from
(select TB_Agency.AgencyName, TB_Personel.PerName, TB_Offer.State, count(TB_Offer.Branch) as OfferCount,
TB_Offer.State
from TB_Agency

join TB_Personel on TB_Agency.AgencyID= TB_Personel.AgencyID

join TB_Offer on TB_Personel.PerID = TB_Offer.PerID

GROUP BY TB_Acente.AgencyName, TB_Personel.PerName, TB_Offer.Branch,TB_Offer.State
)
as
ttt

The only other way I could think of doing such a query would be to use Common Table Expression, but then I have made an assumption that you are running Sql Server