asked on

Need Percentage of Vehicles Checked

I need to also show in this groupby view the percentage of vehicles checked dbo.vehicles.checked

This is a bit feild for either checked or not 1 or 0 1 begin checked.

How would I accomplish this?

SELECT     dbo.auditors.username, dbo.vehicles.id, dbo.vehicles.auditor, dbo.vehicles.auditnumber, dbo.vehicles.auditstatus, dbo.vehicles.vinnumber, 
                      dbo.vehicles.dealerid, dbo.vehicles.vehicleyear, dbo.vehicles.make, dbo.vehicles.model, dbo.vehicles.color, dbo.vehicles.status, dbo.vehicles.cost, 
                      dbo.vehicles.currentbalance, dbo.vehicles.description, dbo.vehicles.mileage, dbo.vehicles.condition, dbo.vehicles.timeseen, dbo.vehicles.timeunseen,
                       dbo.vehicles.solddate, dbo.vehicles.purchaser, dbo.vehicles.imageurl, dbo.vehicles.datenow, dbo.vehicles.totalunits, dbo.vehicles.checked, 
                      dbo.vehicles.totalbalance, dbo.vehicles.auditdate, dbo.dealers.dealername, dbo.dealers.dealernumber, dbo.auditStatus.dispositioncode, 
                      dbo.auditStatus.description AS Expr1, dbo.auditStatus.bucketname, dbo.auditStatus.status AS Expr2, dbo.dealers.city, dbo.dealers.state, 
                      dbo.dealers.id AS dealerid2
FROM         dbo.auditors INNER JOIN
                      dbo.vehicles ON dbo.auditors.id = dbo.vehicles.auditor INNER JOIN
                      dbo.dealers ON dbo.vehicles.dealerid = dbo.dealers.id INNER JOIN
                      dbo.auditStatus ON dbo.vehicles.auditstatus = dbo.auditStatus.id
GROUP BY dbo.auditors.username, dbo.vehicles.id, dbo.vehicles.auditor, dbo.vehicles.auditnumber, dbo.vehicles.auditstatus, dbo.vehicles.vinnumber, 
                      dbo.vehicles.dealerid, dbo.vehicles.vehicleyear, dbo.vehicles.make, dbo.vehicles.model, dbo.vehicles.color, dbo.vehicles.status, dbo.vehicles.cost, 
                      dbo.vehicles.currentbalance, dbo.vehicles.description, dbo.vehicles.mileage, dbo.vehicles.condition, dbo.vehicles.timeseen, dbo.vehicles.timeunseen,
                       dbo.vehicles.solddate, dbo.vehicles.purchaser, dbo.vehicles.imageurl, dbo.vehicles.datenow, dbo.vehicles.totalunits, dbo.vehicles.totalbalance, 
                      dbo.vehicles.auditdate, dbo.vehicles.checked, dbo.dealers.dealername, dbo.dealers.dealernumber, dbo.auditStatus.dispositioncode, 
                      dbo.auditStatus.description, dbo.auditStatus.bucketname, dbo.auditStatus.status, dbo.dealers.city, dbo.dealers.state, dbo.dealers.id

Open in new window

nkhelashvili

You can use this:

With A (username, id, ....., checked, ... dealerid2)
AS
(
SELECT     dbo.auditors.username, dbo.vehicles.id, dbo.vehicles.auditor, dbo.vehicles.auditnumber, dbo.vehicles.auditstatus, dbo.vehicles.vinnumber, 
                      dbo.vehicles.dealerid, dbo.vehicles.vehicleyear, dbo.vehicles.make, dbo.vehicles.model, dbo.vehicles.color, dbo.vehicles.status, dbo.vehicles.cost, 
                      dbo.vehicles.currentbalance, dbo.vehicles.description, dbo.vehicles.mileage, dbo.vehicles.condition, dbo.vehicles.timeseen, dbo.vehicles.timeunseen,
                       dbo.vehicles.solddate, dbo.vehicles.purchaser, dbo.vehicles.imageurl, dbo.vehicles.datenow, dbo.vehicles.totalunits, dbo.vehicles.checked, 
                      dbo.vehicles.totalbalance, dbo.vehicles.auditdate, dbo.dealers.dealername, dbo.dealers.dealernumber, dbo.auditStatus.dispositioncode, 
                      dbo.auditStatus.description AS Expr1, dbo.auditStatus.bucketname, dbo.auditStatus.status AS Expr2, dbo.dealers.city, dbo.dealers.state, 
                      dbo.dealers.id AS dealerid2
FROM         dbo.auditors INNER JOIN
                      dbo.vehicles ON dbo.auditors.id = dbo.vehicles.auditor INNER JOIN
                      dbo.dealers ON dbo.vehicles.dealerid = dbo.dealers.id INNER JOIN
                      dbo.auditStatus ON dbo.vehicles.auditstatus = dbo.auditStatus.id
GROUP BY dbo.auditors.username, dbo.vehicles.id, dbo.vehicles.auditor, dbo.vehicles.auditnumber, dbo.vehicles.auditstatus, dbo.vehicles.vinnumber, 
                      dbo.vehicles.dealerid, dbo.vehicles.vehicleyear, dbo.vehicles.make, dbo.vehicles.model, dbo.vehicles.color, dbo.vehicles.status, dbo.vehicles.cost, 
                      dbo.vehicles.currentbalance, dbo.vehicles.description, dbo.vehicles.mileage, dbo.vehicles.condition, dbo.vehicles.timeseen, dbo.vehicles.timeunseen,
                       dbo.vehicles.solddate, dbo.vehicles.purchaser, dbo.vehicles.imageurl, dbo.vehicles.datenow, dbo.vehicles.totalunits, dbo.vehicles.totalbalance, 
                      dbo.vehicles.auditdate, dbo.vehicles.checked, dbo.dealers.dealername, dbo.dealers.dealernumber, dbo.auditStatus.dispositioncode, 
                      dbo.auditStatus.description, dbo.auditStatus.bucketname, dbo.auditStatus.status, dbo.dealers.city, dbo.dealers.state, dbo.dealers.id
)
 
select
( 
(select count(*) from A where checked=1) * 100 / (select count(*) from A )
) as CheckedPercentage

Open in new window

vonfranzken

ASKER

I got an unable to parse query

vonfranzken

ASKER

In correct syntax near keyword with

WITH A(username, id, ....., checked, ...dealerid2) AS (SELECT dbo.auditors.username, dbo.vehicles.id, dbo.vehicles.auditor, dbo.vehicles.auditnumber,
dbo.vehicles.auditstatus, dbo.vehicles.vinnumber, dbo.vehicles.dealerid,
dbo.vehicles.vehicleyear, dbo.vehicles.make, dbo.vehicles.model, dbo.vehicles.color,
dbo.vehicles.status, dbo.vehicles.cost, dbo.vehicles.currentbalance, dbo.vehicles.description,
dbo.vehicles.mileage, dbo.vehicles.condition, dbo.vehicles.timeseen, dbo.vehicles.timeunseen,
dbo.vehicles.solddate, dbo.vehicles.purchaser, dbo.vehicles.imageurl, dbo.vehicles.datenow,
dbo.vehicles.totalunits, dbo.vehicles.checked, dbo.vehicles.totalbalance, dbo.vehicles.auditdate,
dbo.dealers.dealername, dbo.dealers.dealernumber, dbo.auditStatus.dispositioncode,
dbo.auditStatus.description AS Expr1, dbo.auditStatus.bucketname,
dbo.auditStatus.status AS Expr2, dbo.dealers.city, dbo.dealers.state,
dbo.dealers.id AS dealerid2
FROM dbo.auditors INNER JOIN
dbo.vehicles ON dbo.auditors.id = dbo.vehicles.auditor INNER JOIN
dbo.dealers ON dbo.vehicles.dealerid = dbo.dealers.id INNER JOIN
dbo.auditStatus ON dbo.vehicles.auditstatus = dbo.auditStatus.id
GROUP BY dbo.auditors.username, dbo.vehicles.id, dbo.vehicles.auditor, dbo.vehicles.auditnumber,
dbo.vehicles.auditstatus, dbo.vehicles.vinnumber, dbo.vehicles.dealerid,
dbo.vehicles.vehicleyear, dbo.vehicles.make, dbo.vehicles.model, dbo.vehicles.color,
dbo.vehicles.status, dbo.vehicles.cost, dbo.vehicles.currentbalance, dbo.vehicles.description,
dbo.vehicles.mileage, dbo.vehicles.condition, dbo.vehicles.timeseen, dbo.vehicles.timeunseen,
dbo.vehicles.solddate, dbo.vehicles.purchaser, dbo.vehicles.imageurl, dbo.vehicles.datenow,
dbo.vehicles.totalunits, dbo.vehicles.totalbalance, dbo.vehicles.auditdate, dbo.vehicles.checked,
dbo.dealers.dealername, dbo.dealers.dealernumber, dbo.auditStatus.dispositioncode,
dbo.auditStatus.description, dbo.auditStatus.bucketname, dbo.auditStatus.status, dbo.dealers.city,
dbo.dealers.state, dbo.dealers.id)
SELECT ((SELECT COUNT(*)
FROM A
WHERE checked = 1) * 100 /
(SELECT COUNT(*)
FROM A)) AS CheckedPercentage

WITH A(username, id, ....., checked, ...dealerid2) AS (SELECT     dbo.auditors.username, dbo.vehicles.id, dbo.vehicles.auditor, dbo.vehicles.auditnumber, 
                                                                                                                                        dbo.vehicles.auditstatus, dbo.vehicles.vinnumber, dbo.vehicles.dealerid, 
                                                                                                                                        dbo.vehicles.vehicleyear, dbo.vehicles.make, dbo.vehicles.model, dbo.vehicles.color, 
                                                                                                                                        dbo.vehicles.status, dbo.vehicles.cost, dbo.vehicles.currentbalance, dbo.vehicles.description, 
                                                                                                                                        dbo.vehicles.mileage, dbo.vehicles.condition, dbo.vehicles.timeseen, dbo.vehicles.timeunseen, 
                                                                                                                                        dbo.vehicles.solddate, dbo.vehicles.purchaser, dbo.vehicles.imageurl, dbo.vehicles.datenow, 
                                                                                                                                        dbo.vehicles.totalunits, dbo.vehicles.checked, dbo.vehicles.totalbalance, dbo.vehicles.auditdate, 
                                                                                                                                        dbo.dealers.dealername, dbo.dealers.dealernumber, dbo.auditStatus.dispositioncode, 
                                                                                                                                        dbo.auditStatus.description AS Expr1, dbo.auditStatus.bucketname, 
                                                                                                                                        dbo.auditStatus.status AS Expr2, dbo.dealers.city, dbo.dealers.state, 
                                                                                                                                        dbo.dealers.id AS dealerid2
                                                                                                                 FROM         dbo.auditors INNER JOIN
                                                                                                                                        dbo.vehicles ON dbo.auditors.id = dbo.vehicles.auditor INNER JOIN
                                                                                                                                        dbo.dealers ON dbo.vehicles.dealerid = dbo.dealers.id INNER JOIN
                                                                                                                                        dbo.auditStatus ON dbo.vehicles.auditstatus = dbo.auditStatus.id
                                                                                                                 GROUP BY dbo.auditors.username, dbo.vehicles.id, dbo.vehicles.auditor, dbo.vehicles.auditnumber, 
                                                                                                                                        dbo.vehicles.auditstatus, dbo.vehicles.vinnumber, dbo.vehicles.dealerid, 
                                                                                                                                        dbo.vehicles.vehicleyear, dbo.vehicles.make, dbo.vehicles.model, dbo.vehicles.color, 
                                                                                                                                        dbo.vehicles.status, dbo.vehicles.cost, dbo.vehicles.currentbalance, dbo.vehicles.description, 
                                                                                                                                        dbo.vehicles.mileage, dbo.vehicles.condition, dbo.vehicles.timeseen, dbo.vehicles.timeunseen, 
                                                                                                                                        dbo.vehicles.solddate, dbo.vehicles.purchaser, dbo.vehicles.imageurl, dbo.vehicles.datenow, 
                                                                                                                                        dbo.vehicles.totalunits, dbo.vehicles.totalbalance, dbo.vehicles.auditdate, dbo.vehicles.checked, 
                                                                                                                                        dbo.dealers.dealername, dbo.dealers.dealernumber, dbo.auditStatus.dispositioncode, 
                                                                                                                                        dbo.auditStatus.description, dbo.auditStatus.bucketname, dbo.auditStatus.status, dbo.dealers.city, 
                                                                                                                                        dbo.dealers.state, dbo.dealers.id)
    SELECT     ((SELECT     COUNT(*)
                              FROM         A
                              WHERE     checked = 1) * 100 /
                                (SELECT     COUNT(*)
                                  FROM          A)) AS CheckedPercentage

Open in new window

nkhelashvili

You will be unable of cource, you must fill all fields, I wrote three dots in that place which you must fill by field names

vonfranzken

ASKER

Ok will give it a go and post back shortly.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial