SQL query - mod function - how can I use it to group results?

sql

Solution

It sounds like this is what you're looking for -- use the % operator to perform a Mod:

select a % 2, sum(b)
from yourtable
group by a % 2

Condensed SQL Fiddle Demo

To apply this to your query, try this:

SELECT PRTH.PostDate, HRRM.LastName, HRRM.FirstName, 
    Sum(PRTH.Hours) AS 'Sum of Hours', (PRTH.Shift % 2) As 'Night Shift'
FROM Viewpoint.dbo.HRRM HRRM
    INNER JOIN Viewpoint.dbo.PRTH PRTH
        ON PRTH.Employee = HRRM.PREmp AND PRTH.JCCo = HRRM.PRCo
GROUP BY PRTH.PostDate, HRRM.LastName, HRRM.FirstName, PRTH.Shift % 2

Also notice, I replaced your Joins with the `INNER JOIN` syntax -- this is preferable. See this post for more information (https://stackoverflow.com/a/1018825/1073631)

Problem

I'm very new to querying so apologies if this is a simple question. Note this question is related to Microsoft Query as I am pulling the results through to a report. I currently have a table with a number of columns, including one column that is an integer value between 1 and 6. I am trying to group by on this column based on whether the value in the column is even or odd but am just not sure how to accomplish this given my knowledge (a bit of background as to why I want this: the table I am using relates to timesheet entries with the shift column I am referring to having odd values for day shift and even values for night shift). If someone could help this would be great. Currently, I have the following code: ``` SELECT PRTH.PostDate, HRRM.LastName, HRRM.FirstName, Sum(PRTH.Hours) AS 'Sum of Hours', (PRTH.Shift Mod 2) As 'Night Shift' FROM Viewpoint.dbo.HRRM HRRM, Viewpoint.dbo.PRTH PRTH WHERE PRTH.Employee = HRRM.PREmp AND PRTH.JCCo = HRRM.PRCo GROUP BY PRTH.PostDate, HRRM.LastName, HRRM.FirstName, Night Shift ``` It is the `Mod` function that I am having trouble with as I am not sure how to define this and then also how to use this in the group by function. Thanks in advance

Original source

Related problems