Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

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

0
vonfranzken
Asked:
vonfranzken
  • 3
  • 2
1 Solution
 
nkhelashviliCommented:
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

0
 
vonfranzkenAuthor Commented:
I got an unable to parse query
0
 
vonfranzkenAuthor 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

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
nkhelashviliCommented:
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
0
 
vonfranzkenAuthor Commented:
Ok will give it a go and post back shortly.
0
 
Scott PletcherSenior DBACommented:
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
 
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now