CODEWITHSUNDEEP
sql-server-2008
Pardeep Kumar
Pardeep Kumar

Reputation: 83

can anybody help me how to do this in sql server?

select 
    Table1.empName,
    sum(case when saleStatus = 'shipped' and table1.saleApproved = 'yes' 
                then 1
                else 0 
           end) as count_shipped,
    sum(case when saleStatus in ('Pending', 'awaiting payment', 'backorder') 
                then 1 
                else 0 
           end) as count_pending 
from 
   Table1
group by 
   Table1.empName

The output of this query is

A   0   0
B   0   0
C   0   0
D   0   2
E   0   0
F   0   0
G   0   1
H   0   1
J   0   1

And the second query is 

select 
    empname,
    sum(timeordertook) 
from 
    Table11  
group by 
    empName
order by 
    empName

and the output of this query is 

A   2195
B   2751
C   719
D   2270
E   1539
F   381
G   3793
H   1186
I   267
J   895

I want to add second column of the second query in the first query. Can anybody help me how to do this in SQL Server?

Upvotes: 0

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You an do this with a join. Because both queries are being aggregated, you need to be a little careful. here is one method:

select t1.empName,
       sum(case when t1.saleStatus = 'shipped' and t1.saleApproved = 'yes' then 1 else 0
           end) as count_shipped,
       sum(case when t1.saleStatus in ('Pending', 'awaiting payment', 'backorder') then 1 else 0       
           end) as count_pending,
       max(t11.sumtimeordertook)
from Table1 t1 left join
     (select empname, sum(timeordertook) as sumtimeordertook
      from Table11 t11
      group by empName
     ) t11
     on t1.empname = t11.empname
order by t1.empName
group by t1.empName

Upvotes: 1

Related Questions