Need report using aggregate funtions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need report using aggregate funtions

Author

Topic

NijelJ22
Starting Member

18 Posts

Posted - 2008-08-22 : 02:16:44

Hi,

I am on learning curve to SQL Server. I want to fetch data from a report. Tables contains different fields two of them are vendorid and ackflag. Vendorid contains id of different vendors and ackflag contains 0 or 1. I need a report where in i can get the all the vendorids, count of all ackflag and then all records that have "1" as a value in ackflag field.

Please help..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-22 : 02:19:53

SELECT vendorid,
COUNT(ackflag) as Total,
COUNT(CASE WHEN ackflag=1 THEN ackflag ELSE NULL END) AS ack
FROM YourTable
GROUP BY vendorid

NijelJ22
Starting Member

18 Posts

Posted - 2008-08-22 : 02:25:28

Thank u sir.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-22 : 02:32:38

quote:
Originally posted by NijelJ22

Thank u sir.

You're welcome

you could also consider doing this in your report application.

NijelJ22
Starting Member

18 Posts

Posted - 2008-08-22 : 03:06:25

I need further help ...

I have two tables vendor and purchaseorder. Vendor contains Vendorname, vendorid and vendortype. Purchaseorder contains ackflag and vendorid field. Ackflag contains 0 and 1 only.

I need following o/p..

Vendor Name, Vendor Type, TOtal POs, PO Accepted, PO Pending, Percentage Effectiveness

Hint...

TOtal POs = Total Count of ackflag
PO Accepted = COunt of ackflag where it contains 1
PO Pending = COunt of ackflag where it contains 0
Percentage Effectiveness = (PO Accepted + PO Pending) / PO Hosted

Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-22 : 03:57:28

Do like below

SELECT v.VendorName,v.VendorType,
COUNT(po.PurchaseOrderID) AS TotalPOs,
COUNT(CASE WHEN po.ackflag=1 THEN po.PurchaseOrderID ELSE NULL END) AS POsAccepted,
COUNT(CASE WHEN po.ackflag=0 THEN po.PurchaseOrderID ELSE NULL END)
AS POsPending,
(COUNT(CASE WHEN po.ackflag=1 THEN po.PurchaseOrderID ELSE NULL END)+COUNT(CASE WHEN po.ackflag=0 THEN po.PurchaseOrderID ELSE NULL END))*1.0/POHosted AS Percentage Effectiveness 
FROM Vendors v
INNER JOIN purchaseorder po
on po.VendorID=v.VendorID
GROUP BY v.VendorName,v.VendorType

Also replace POHosted by actual calaculation to find it ( i dont know how to determine it as you've provided the info)

NijelJ22
Starting Member

18 Posts

Posted - 2008-08-22 : 04:43:26

Thanks that did the trick for me.

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources