Insert Into Temp table multiple select statements

Question

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?

Please show some sample data and expected results.

Dale K
– Dale K

2021-03-06 02:13:06 +00:00
Commented Mar 6, 2021 at 2:13 — Dale K
– Dale K, Commented Mar 6, 2021 at 2:13
@Dale K, I added what expected output should look like

Nebelz Cheez
– Nebelz Cheez

2021-03-06 02:21:58 +00:00
Commented Mar 6, 2021 at 2:21 — Nebelz Cheez
– Nebelz Cheez, Commented Mar 6, 2021 at 2:21
Also need sample data used to generate that/.

Dale K
– Dale K

2021-03-06 02:23:02 +00:00
Commented Mar 6, 2021 at 2:23 — Dale K
– Dale K, Commented Mar 6, 2021 at 2:23

Gordon Linoff · Accepted Answer · 2021-03-06 02:52:30Z

1

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;

edited Mar 6, 2021 at 2:52

answered Mar 6, 2021 at 2:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Nebelz Cheez Over a year ago

Right, but I'm trying to join ProjectManager_UserID with UserID in User table so I can get, Name and job Title, as listed in the top of the questions. So I can see name, job title and AcceptanceRate

Nebelz Cheez Over a year ago

Wait, Even though its selecting as QuoteAccepted, it says No column Name ? For Bottom Query its showing QuoteAccepted but not for top Query

Gordon Linoff Over a year ago

The bottom query is just for the user ids. The first query answers the question you have asked here.

Nebelz Cheez Over a year ago

nvmd, Its Working!

Nebelz Cheez Over a year ago

Hey Gordon, Would it be possible to show result for all Managers instead of UserID=8? so basically a mix of top and bottom query? I stared at it for an hour but couldn't figure it out. If you can help, that would be kind of you :)

|

Collectives™ on Stack Overflow

Insert Into Temp table multiple select statements

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related