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