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
rfedorovAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
<No, guys, it is not working and it does not supposed to work your way, sorry to say.it gives the same numbers >

the query i posted gives this result


Inspectors      MaxOfMonth       Category        SumOfTotalMonthly      MaxOfTotalMonthly
John                       Mar                MILEAGE                      8000                                    5000


now, tell us what is wrong ?


.
0
 
awking00Commented:
Is TotalMonthly of 5,000 because it is the maximum total for any give month?
0
 
rfedorovAuthor Commented:
thank you for such fast respond, no, just total number of miles per month
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
awking00Commented:
If so -
select inspector, category, sum(TotalMonthly) as YTD, max(TotalMonthly) as TotalMonthly
from yourtable
group by inspector, category;
0
 
awking00Commented:
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)?
0
 
COACHMAN99Commented:
is total monthly and ytd not the same thing?
0
 
rfedorovAuthor Commented:
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
0
 
rfedorovAuthor Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
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;
0
 
SharathConnect With a Mentor Data EngineerCommented:
select inspector, category, sum(TotalMonthly) as YTD, last(TotalMonthly) as TotalMonthly
from yourtable
group by inspector, category;

Open in new window

0
 
rfedorovAuthor Commented:
No, guys, it is not working and it does not supposed to work your way, sorry to say.it gives the same numbers
0
 
rfedorovAuthor Commented:
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
0
 
rfedorovAuthor Commented:
To: Rey Obrero
Thank you very much, nothing is wrong...everything is great...Working...it took me a while to bring "real" data.
0
All Courses

From novice to tech pro — start learning today.