CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
dmorgan20
dmorgan20

Reputation: 363

Divide one by another in SQL

I think I am over complicating this.

I'm not fantastic at SQL so I need to use the query builder at times.

What I am trying to achieve:

I want my query to show me:

Managers name

Number of times the managers name is in the table

The number of disputes that equal TRUE

disputes divided by total times manager is in the table

Current SQL

SELECT DISTINCT tbl_Quality_Disputes.LoggedDate, tbl_Quality_Disputes.Manager, Count(tbl_Quality_Disputes.Manager) AS CountOfManager, [Decision]/[Manager] AS Success_Rate
    FROM tbl_Quality_Disputes
    GROUP BY tbl_Quality_Disputes.LoggedDate, tbl_Quality_Disputes.Manager, tbl_Quality_Disputes.Decision, [Decision]/[Manager]
    HAVING (((tbl_Quality_Disputes.Decision)="Approved"));

Where am I going wrong with this?

Thanks in advance

Upvotes: 0

Views: 2556

Answers (2)

Stefano Zanini
Stefano Zanini

Reputation: 5916

Let's start from the grouping you need. In your result you want a row for each manager, so that's it, definitely no Decision.

Then, you're right in not placing the filtering on the decision in the WHERE clause, because that would remove the rows from the count(*) as well, but HAVING is meant for conditions on aggregated fields, so you cannot put it there either. Since you need that filtering on one of the aggregations but not on the other, you can use a case inside the aggregation where it's needed.

Finally, the calculation of the success rate should be based on the counts, not on the Manager and Decision fields.

I ignored the LoggedDate field as its meaning is not clear, but if you explain it a bit I will edit my query so that it can fit in

SELECT  Manager,
        COUNT(*) AS CountOfManager,
        SUM(case when Decision = "Approved" then 1 else 0 end) AS Decision,
        SUM(case when Decision = "Approved" then 1 else 0 end) / Count(*) AS Success_Rate
FROM    tbl_Quality_Disputes
GROUP BY  Manager

As a side note, I removed the table name from all fields prefixes because when using a single table it's not needed and makes the query much more verbose. When using two or more tables it's good practice to prefix the column names, but you can give aliases to the tables to make everything a bit more compact.

Edit

Since the LoggedDate will be the same for all the rows of the same Manager, you can either add to the GROUP BY or aggregate it with a MIN or MAX (I prefer the latter option, I find it more robust).

Regarding the error you got, that's probably because Access doesn't support the CASE expression (sorry, didn't know that). I replaced it with IIF, now it shold work.

SELECT  Manager,
        MAX(LoggedDate) AS MaxLoggedDate,
        COUNT(*) AS CountOfManager,
        SUM(IIF(Decision = "Approved", 1, 0)) AS CountOfDecision,
        SUM(IIF(Decision = "Approved", 1, 0)) / Count(*) AS Success_Rate
FROM    tbl_Quality_Disputes
GROUP BY  Manager

Upvotes: 1

Jim Macaulay
Jim Macaulay

Reputation: 5141


Try using below query,

SELECT tbl_Quality_Disputes.LoggedDate, tbl_Quality_Disputes.Manager, 
Count(tbl_Quality_Disputes.Manager) AS CountOfManager, [Decision]/[Manager] AS Success_Rate
    FROM tbl_Quality_Disputes
WHERE tbl_Quality_Disputes.Decision="Approved"
    GROUP BY tbl_Quality_Disputes.LoggedDate, tbl_Quality_Disputes.Manager, 
tbl_Quality_Disputes.Decision, [Decision]/[Manager];

Kindly provide sample data and expected output

Upvotes: 0

Related Questions