x
Solved

# Help with query

Posted on 2014-04-03
Medium Priority
244 Views
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
0
Question by:rfedorov
• 6
• 3
• 2
• +2

LVL 32

Expert Comment

ID: 39976052
Is TotalMonthly of 5,000 because it is the maximum total for any give month?
0

Author Comment

ID: 39976060
thank you for such fast respond, no, just total number of miles per month
0

LVL 32

Expert Comment

ID: 39976063
If so -
select inspector, category, sum(TotalMonthly) as YTD, max(TotalMonthly) as TotalMonthly
from yourtable
group by inspector, category;
0

LVL 32

Expert Comment

ID: 39976077
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

LVL 7

Expert Comment

ID: 39976083
is total monthly and ytd not the same thing?
0

Author Comment

ID: 39976092
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

Author Comment

ID: 39976125
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

LVL 120

Expert Comment

ID: 39976153
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

LVL 41

Assisted Solution

Sharath earned 200 total points
ID: 39976373
``````select inspector, category, sum(TotalMonthly) as YTD, last(TotalMonthly) as TotalMonthly
from yourtable
group by inspector, category;
``````
0

Author Comment

ID: 39976428
No, guys, it is not working and it does not supposed to work your way, sorry to say.it gives the same numbers
0

Author Comment

ID: 39976438
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

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1800 total points
ID: 39976501
<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

Author Comment

ID: 39978180
To: Rey Obrero
Thank you very much, nothing is wrong...everything is great...Working...it took me a while to bring "real" data.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.