Need Assistance with Streamlining/Reducing # of Queries to Output "Percentages"

[skydivetom]

Happy Friday:



I need some assistance with streamlining/reducing # of queries to output a "percentage query".

Objects I Have/Need to Keep:
- Tables [T1] and [T2]
- Queries [Q01_Complete] and [Q02_Remaining]

Objects I'd Like to Streamline/Simplify/Reduce:
- Q11_CountComplete
- Q12_CountRemaining
- Q13_CountTotal
- Q20_Percentage

Existing Process:
A. Query "Q11" pulls record count from [Q01_Complete] ... 61 records
B. Query "Q12" pulls record count from [Q02_Remaining] ... 13 records
C. Query "Q13" sums up record counts from "Q11" and "Q12" ... 74 records
D. Query "Q14" brings in all record counts from "Q11", "Q12", "Q13" and then generates the percentage calculation as shown below:

PercentComplete: 61/74 = 82.4%
PercentRemaining: 13/74 = 17.6%
PercentTotal: 61/74 + 13/74 = 100.0%

Although the calculation(s) itself are correct, it currently requires 4 queries (Q11, Q12, Q13, Q20) to produce the results.

My question(s):
Does anyone know of a better/streamlined process that allows me to reduce the 4 queries to some smaller number? Can some of the "helper" queries be combined? If so, how?

[ranman256]

They dont charge you by the query. If 4 queries works, use it.

[skydivetom]

In this case, I'd like to "lose money".

[CJ_London]

Assuming Q01 and Q02 use the same source, you could do it with 2 queries

[skydivetom]

CJ_London:

Yes, in my actual DB, both of them use the same source. Multi-object query with n joins.

For testing purposes only, however, I split actual query into temp tables T1 and T2. So, for demo purposes, T1 and T2 could be merged.

[CJ_London]

consider using sum instead of count

e.g.

SELECT completed/(completed+remaining) as PCComplete, remaining/(completed+remaining) as PCRemaining, (completed+remaining)/(completed+remaining) as PCTotal
FROM (select -sum(Complete=true) as completed, -sum(complete=false) as remaining from myqry) as qry

[skydivetom]

CJ_London:

I have merged T1 and T2 and created new table T3 now with 74 records. I'm not entirely certain how to apply the proposed SQL to single table T3 only having one column "MAPPING_STATUS".

What would the SQL look like based on T3?

[CJ_London]

your T3 table has 3 values, not what you described before so I haven't done the total since not clear whether the 'not mapped' values should be included, but the query is pretty much the same

SELECT [proposed]/([proposed]+[verified]) AS PCProposed, [Verified]/([proposed]+[verified]) AS PCVerified
FROM (SELECT -Sum([MAPPING_STATUS]='proposed') AS proposed, -Sum([MAPPING_STATUS]='verified') AS verified FROM T3)  AS Q;

produces this result

PCProposed	PCVerified
52.46%	47.54%

[skydivetom]

CJ_London:

I definitely love the idea of this streamlined query.

"Proposed" (32 records) and "Verified" (29 records) should be combined (61 records).

Given denominator of 74 (all records), "PCProposedVerified" should = 82.4%. How should the SQL be modified to give me a 82.4% vs. 17.6% (Not Mapped) distribution?

[skydivetom]

CJ_London:

Your proposed structure/streamlining works great!! Many thanks!

SELECT [proposed]/([proposed]+[verified]) AS PCProposed, [Verified]/([proposed]+[verified]) AS PCVerified, [not_mapped]/([proposed]+[verified]+[not_mapped]) AS PCNotMappedYet, ([proposed]+[verified])/([proposed]+[verified]+[not_mapped]) AS PCComplete
FROM (SELECT -Sum([MAPPING_STATUS]='proposed') AS proposed, -Sum([MAPPING_STATUS]='verified') AS verified, -Sum([MAPPING_STATUS]='not mapped yet') AS not_mapped FROM T3)  AS Q;