Query with percentages (Part Deux) and grouping

[bigal.nz]

Hi All,

I have a query which I need to refine. Currently it calculates a percentage across all records in the results. It actually needs to calculate percentage based on total volume by month AND area.

SaleYN is discrete and has possible values 'Yes', 'No' and ''

*** Mas TABLE ***
DateStart, Station, SaleYN, OtherFields
7/3/17, Sacremento, Yes,
9/5/17, Alameda, No,
11/3/17, Brentwood, ,
10/5/17, Sacremento, No,
11/9/17, Coronado, No,
12/12/17, Del Mar, Yes,

*** StationCounty TABLE ***
Station, County, Table
Sacremento, Bay Area, North
Alameda, Los Angeles, South
Brentwood, Los Angeles, South
Corondao, San Deigo, South
Del Mar, San Deigo, South

My current attempt at the query is :

SELECT StationCounty.County, SaleYN, format([DateStart], 'mmm yy'),
Count(*) AS CountSale_MAS_NTH, ROUND ( COUNT (SaleYN) / (SELECT COUNT (SaleYN) FROM MAS_NTH INNER JOIN StationCounty ON MAS_NTH.Station = StationCounty.Station) , 3)*100 AS PERCENT_Sale_MAS_NTH
FROM MAS_NTH INNER JOIN StationCounty ON MAS_NTH.Station = StationCounty.Station
WHERE SaleYN='Yes' AND DateStart > #7/3/2017#
GROUP BY StationCounty.County, SaleYN, format([DateStart], 'mmm yy');

Which gives results:

County, SaleYN, DatePart, CountSale_MAS_NTH, Percent_Sale_MAS_NTH,
Bay Area, Yes, Jul 17, 1, 22.1
Bay Area, Yes, Aug 17, 3, 12.2
San Deigo, Yes, Jul 17, 4, 36.3
San Deigo, Yes, Aug 17, 2, 28.4
etc

(Note the above results are made up and do not correlate with the sample table).

The issue I have is in trying to get the percentage correct. It should express the percentage of 'Yes' total of all records for a given county. Currently I think its a percentage of all records (no county or month grouping on percentage calc)

Phew - hope this makes sense.

Cheers

Al

 

[plog]

First, I'm not entirely certain what to be the denominator of your percentage. The numerator seems clear cut--all Yes records for a county/Datestart permuation.

What exactly is the denominator? Check out this sample data:

6/1/17, Sacremento, Yes
7/3/17, Sacremento, Yes
9/5/17, Sacremento, Yes
9/5/17, Sacremento, No


In your results you will have a 9/5/17, Bay Area record. For that record the numerator will be 1. What will the denominator be? 1, 2, 3 or 4?

Ultimately, the term for what you want is a 'correlated subquery' and a microsoft example is here: https://msdn.microsoft.com/en-us/library/office/aa217680(v=office.11).aspx

Essentially the subquery is able to reference data in the main query to use as a filter. That's going to be a trick you use in your subquery to pull just the data for the county you are working with. Answer my question about the denominator and we can work towards that correlated subquery.

 

[bigal.nz]

First, I'm not entirely certain what to be the denominator of your percentage. The numerator seems clear cut--all Yes records for a county/Datestart permuation.

What exactly is the denominator? Check out this sample data:

6/1/17, Sacremento, Yes
7/3/17, Sacremento, Yes
9/5/17, Sacremento, Yes
9/5/17, Sacremento, No


In your results you will have a 9/5/17, Bay Area record. For that record the numerator will be 1. What will the denominator be? 1, 2, 3 or 4?

Ultimately, the term for what you want is a 'correlated subquery' and a microsoft example is here: https://msdn.microsoft.com/en-us/library/office/aa217680(v=office.11).aspx

Essentially the subquery is able to reference data in the main query to use as a filter. That's going to be a trick you use in your subquery to pull just the data for the county you are working with. Answer my question about the denominator and we can work towards that correlated subquery.

Hi,

Sacremento has two records in September but only one is answered yes, so the calculation would be 2/1 = 50%.

Denominator therefore is 1. It the percentage of records for a given month and county that are answered yes.

Hope that makes sense?

 

[plog]

2/1 = 50%.

I'm an American and this site has shown me a lot of unusual differences between here and the UK, but is that how division is written in New Zealand? For me and even the queen, 2/1 = 2 not .5. The numerator is the number being divided (and on top) and the denominator is the divisor (and on bottom). Did you just mispeak or did I learn something culturally?

 

[bigal.nz]

I'm an American and this site has shown me a lot of unusual differences between here and the UK, but is that how division is written in New Zealand? For me and even the queen, 2/1 = 2 not .5. The numerator is the number being divided (and on top) and the denominator is the divisor (and on bottom). Did you just mispeak or did I learn something culturally?

Duh! My bad. I meant 1/2 = 0.5. Denom = 2.

 

[plog]

No problem. Just as long as you properly pronounce what soda cans are made of.

Also, because of your answer, this isn't as complex as I initially thought and can be done without a subquery, just an IIF statement:

SELECT County, DateStart, SUM(IIf(SaleYN='Yes', 1,0)) / COUNT(County)  AS PercentTotal
FROM MAS_NTH
INNER JOIN StationCounty ON MAS_NTH.Station = StationCounty.Station
GROUP BY County, DateStart

That should do it. You should be able to open it up in design view to add any other fields you need.

 

[bigal.nz]

No problem. Just as long as you properly pronounce what soda cans are made of.

Also, because of your answer, this isn't as complex as I initially thought and can be done without a subquery, just an IIF statement:

SELECT County, DateStart, SUM(IIf(SaleYN='Yes', 1,0)) / COUNT(County)  AS PercentTotal
FROM MAS_NTH
INNER JOIN StationCounty ON MAS_NTH.Station = StationCounty.Station
GROUP BY County, DateStart

That should do it. You should be able to open it up in design view to add any other fields you need.

Thanks, I tried that but the results are a bit odd.

The percent column if full of 1's or 0's?

 

[Mark_]

I would double check your data...
Sacramento is in Sacramento county. Placing in in "Bay Area" is comparable to saying "Auckland is just up the road from Christchurch". Similar distance to.

On the plus side a friend of mine runs an "puzzle room" in the area.

 

[bigal.nz]

I would double check your data...
Sacramento is in Sacramento county. Placing in in "Bay Area" is comparable to saying "Auckland is just up the road from Christchurch". Similar distance to.

On the plus side a friend of mine runs an "puzzle room" in the area.

Small world. The data has been obfuscated and made up. Sorry to the folks in CA

Churistchurch who ;-)

 

[Mark_]

Small world. The data has been obfuscated and made up. Sorry to the folks in CA

Churistchurch who ;-)

No problem. Then again I do know a Bi-Gal in Sacramento, so all's good!

 

[plog]

Can you upload some data or type in some examples? Maybe a screenshot of the data from the tables and the results in the query?

Did you bring in more fields?