4

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pmc086
  • 55
  • 1
  • 6
  • While you're learning, read this: [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was discontinued with the ANSI-**92** SQL Standard (**20 years** ago!). – marc_s Mar 27 '13 at 06:01
  • Thanks - I shall have a read and note for future use. You can blame MS Query for the bad joins - thats the code it generates when you link the tables using the drag and drop relationship picker. Didn't see much point in changing it given that the query I was using was not complicated and there is unlikely to be any editing required once the query was completed. – pmc086 Mar 27 '13 at 06:08

1 Answers1

3

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)

Community
  • 1
  • 1
sgeddes
  • 62,311
  • 6
  • 61
  • 83