# ms access 2013 - SQL Calculate Percentage in Group By

I have an SQL query that is used as the basis for a report. The report shows the amount of fuel used grouped by Year, Month and Fuel Type. I would like to calculate the percentage of the total for each fuel type, but I'm not having much luck. In order to calculate the percentage of the whole, I need to be able to get the total amount of fuel used regardless of the group it is in and I can't seem to figure out how to do this. Here is my query:

``SELECT Year([DT1].[TransactionDate]) AS [Year], Month([DT1].[TransactionDate]) AS [Month], DT1.FuelType, Format(Sum(DT1.Used),"#.0") AS [Total Used],FROM (SELECT TransactionDate, FuelType, Round([MeterAfter]-[MeterBefore],2) AS Used FROM FuelLog) AS DT1WHERE (((DT1.TransactionDate) Between [Start Date] And [End Date]))GROUP BY Year([DT1].[TransactionDate]), Month([DT1].[TransactionDate]), DT1.FuelTypeORDER BY Year([DT1].[TransactionDate]), Month(DT1.TransactionDate), DT1.FuelType;``

I tried adding the following as a subquery but I get an error saying the subquery returns more than one result.

``(SELECT Sum(Round([MeterAfter]-[MeterBefore],2)) AS TestFROM FuellogWHERE Year([Year]) and Month([Month])GROUP BY Year([TransactionDate]), Month([TransactionDate]))``

Once I get the total of all fuel I will need to divide the amount of fuel used by the total amount of both fuel types. Should I be approaching this a different way?

Try this

``````SELECT A.[Year]
,A.[Month]
,A.[FuelType]
,A.[Total Used]
,(A.[Total Used] / B.[Total By Year Month]) * 100 AS Percentage
FROM
(
SELECT Year([DT1].[TransactionDate]) AS [Year]
, Month([DT1].[TransactionDate]) AS [Month]
, DT1.FuelType
, Format(Sum(DT1.Used),"#.0") AS [Total Used]
FROM (
SELECT TransactionDate
, FuelType
, Round([MeterAfter]-[MeterBefore],2) AS Used
FROM FuelLog
)  AS DT1
WHERE (((DT1.TransactionDate) Between [Start Date] And [End Date]))
GROUP BY Year([DT1].[TransactionDate]), Month([DT1].[TransactionDate]),     DT1.FuelType
ORDER BY Year([DT1].[TransactionDate]), Month(DT1.TransactionDate),     DT1.FuelType
) A
INNER JOIN
(
SELECT Sum(Round([MeterAfter]-[MeterBefore],2)) AS [Total By Year Month]
, Year([TransactionDate]) AS [Year]
, Month([TransactionDate])) AS [Month]
FROM Fuellog
GROUP
BY Year([TransactionDate])
, Month([TransactionDate]))
) B
ON A.[Year] = B.[Year]
AND A.[Month] = B.[Month]
``````

You need to join to the totals -- something like this (untested might have typos)

``````SELECT
Year([DT1].[TransactionDate]) AS [Year],
Month([DT1].[TransactionDate]) AS [Month],
DT1.FuelType,
Format(Sum(DT1.Used),"#.0") AS [Total Used],
(Sum(DT1.Used) / FT.Total) * 100 AS Percent
FROM (
SELECT
TransactionDate,
FuelType,
Round([MeterAfter]-[MeterBefore],2) AS Used
FROM FuelLog
)  AS DT1
JOIN (
SELECT
Sum(Round([MeterAfter]-[MeterBefore],2)) AS Total
FuelType
FROM Fuellog
WHERE TransactionDate Between [Start Date] And [End Date]
GROUP BY FuelType
) FT ON DT1.FuelType = FT.FeulType
WHERE DT1.TransactionDate Between [Start Date] And [End Date]
GROUP BY Year([DT1].[TransactionDate]), Month([DT1].[TransactionDate]), DT1.FuelType, FT.Total
ORDER BY Year([DT1].[TransactionDate]), Month(DT1.TransactionDate), DT1.FuelType, FT.Total;
``````