Help with query
Please help
I have a simple table
Inspectors Month Category TotalMonthly
John Jan MILEAGE 1,000
John Feb MILEAGE 2,000
John March MILEAGE 5,000
i need some help with making the summary query showing:
Inspectors Category YTD TotalMonthly
John MILEAGE 8,000 5,000
I have a simple table
Inspectors Month Category TotalMonthly
John Jan MILEAGE 1,000
John Feb MILEAGE 2,000
John March MILEAGE 5,000
i need some help with making the summary query showing:
Inspectors Category YTD TotalMonthly
John MILEAGE 8,000 5,000
awking00
Is TotalMonthly of 5,000 because it is the maximum total for any give month?
ASKER
thank you for such fast respond, no, just total number of miles per month
If so -
select inspector, category, sum(TotalMonthly) as YTD, max(TotalMonthly) as TotalMonthly
from yourtable
group by inspector, category;
select inspector, category, sum(TotalMonthly) as YTD, max(TotalMonthly) as TotalMonthly
from yourtable
group by inspector, category;
That doesn/t explain how the 5,000 gets into your query summary. Is it because it's the latest TotalMonthly available (i.e. from the latest month)?
is total monthly and ytd not the same thing?
ASKER
Inspectors Category YTD TotalMonthly(last month mileage)
John MILEAGE 8,000 5,000
no, it does not work the way i want... with your query i am getting the same number
John MILEAGE 8,000 5,000
no, it does not work the way i want... with your query i am getting the same number
ASKER
simple count YTD: should 1000+2000+5000 gives 8000. This is year to date for three month and number for march which is latest month is 5000. I assume we need to use switch function
SELECT Travel.Inspectors, Max(Travel.Month) AS MaxOfMonth, Travel.Category, Sum(Travel.TotalMonthly) AS SumOfTotalMonthly, Max(Travel.TotalMonthly) AS MaxOfTotalMonthly
FROM Travel
GROUP BY Travel.Inspectors, Travel.Category;
FROM Travel
GROUP BY Travel.Inspectors, Travel.Category;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, guys, it is not working and it does not supposed to work your way, sorry to say.it gives the same numbers
ASKER
we need to use Switch function, which will take care of the Month
Count everything for all month and count only month where is March...
Switch does that, i can not figure out how
Count everything for all month and count only month where is March...
Switch does that, i can not figure out how
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To: Rey Obrero
Thank you very much, nothing is wrong...everything is great...Working...it took me a while to bring "real" data.
Thank you very much, nothing is wrong...everything is great...Working...it took me a while to bring "real" data.