CODEWITHSUNDEEP
sqlsql-servert-sql
Nebelz Cheez
Nebelz Cheez

Reputation: 307

Insert Into Temp table multiple select statements

I have 2 select statements below which I would like to show the output like below. Currently, I am unable to join first query with second.

fanme   lname  JobTitle   AcceptanceRate
Jim    Cary     Manager    0.666666

   select fname, lname, JobTitle
   from [User]
   where userID=8


Select convert(decimal, x.QuoteAccepted) / y.AllQuotes as  AcceptanceRate 
from  (
    select count(*) as QuoteAccepted
    from Quote
    where ProjectManager_UserID=8 and QuoteStatusID=6
) x
join (
    select count(*) as AllQuotes
    from Quote
    where ProjectManager_UserID=8
) 
y on 1=1

I tried creating temp tables and insert into but it kept showing error:

Column name or number of supplied values does not match table definition.

is there any possible way to do this?

Upvotes: 0

Views: 561

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

You can use conditional aggregation:

select u.*,
       (select avg(case when q.QuoteStatusID = 6 then 1.0 else 0 end) as QuoteAccepted
        from Quote q
        where q.ProjectManager_UserID = u.userId
       ) as AcceptanceRate
from users u
where u.UserID = 8

If you want this for all project managers:

select ProjectManager_UserID,
       avg(case when QuoteStatusID = 6 then 1.0 else 0 end) as AcceptanceRate
from Quote
group by ProjectManager_UserID;

Upvotes: 1

Related Questions