SQL one-time query a field statistic results under different conditions

Refer to this article: 51764100, thank the original author

There are two tables, respectively, the "operator" and "documents", according to the different types of documents to classify the summary ( sales orders, sales return orders, the number of pens and amounts ), and displayed in the same table, do not want to be used as two times the method of query and merge, research, finally FIX:

D_employee table

D_bilndx table

The code is as follows:

SelectB.inputid asticket number, e.fullname. asopen a single member,IsNull( (Select Count(*)           fromD_bilndxwhereDraft=3  andBiltype= A  andD_bilndx.inputid=e.id),0) as 'sales Open a single pen number',        IsNull( (Select sum(D_bilndx.amount) fromD_bilndxwhereDraft=3  andBiltype= A  andD_bilndx.inputid=e.id),0) as 'Sales Billing Amount',        IsNull( (Select Count(*)           fromD_bilndxwhereDraft=3  andBiltype= -  andD_bilndx.inputid=e.id),0) as 'number of sales chargebacks',        IsNull( (Select sum(D_bilndx.amount) fromD_bilndxwhereDraft=3  andBiltype= -  andD_bilndx.inputid=e.id),0) as 'Sales Return amount',        Count(B.biltype) astotal number of open orders,sum(B.amount) asOpen a single amount fromD_bilndx asb Left JoinD_employee ase onB.inputid=e.idwhereB.draft=3  and(B.biltype= A orB.biltype= - )Group  byB.inputid, E.fullname, e.id

Get the perfect result:

SQL one-time query a field statistic results under different conditions