We help IT Professionals succeed at work.

Need Percentage of Vehicles Checked

vonfranzken
vonfranzken asked
on
Medium Priority
305 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

CERTIFIED EXPERT

Commented:
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

Author

Commented:
I got an unable to parse query

Author

Commented:
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

CERTIFIED EXPERT

Commented:
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

Author

Commented:
Ok will give it a go and post back shortly.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
The % of vehicles checked overall?  Or by dealer?  Or by ...?

Here's how to add the overall % to every row:

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,
                      MAX(checkedPct) AS checkedPct
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 CROSS JOIN
                      (SELECT SUM(CASE WHEN checked = 1 THEN 1 ELSE 0 END) * 100 / COUNT(*) AS checkedPct
                       FROM dbo.vehicles.checked) AS checkedPct                      
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
 

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.