tags:

views:

376

answers:

8

i see a query like

select * from Table1 
group by Step 
having count(*) % 2 = 1

what is the trick about "having count(*) % 2 = 1"

can anyone explain?

edit: what are the common usage areas?

+15  A: 

Would that not be checking if you have an odd number of entries per step?

Milan Ramaiya
+20  A: 

Well % is the modulo operator, which gives the remainder of a division so it would give 0 when the number is exactly divisible by 2 (even) and 1 when not (e.g. it is odd). So the query basically selects elements for which count is odd (as said above).

ternaryOperator
I like this answer. Since it gives a full answer to the problem.
Hazior
ok i understand what is module operator. But i'm not asking that!what is the trick, why a programmer uses this statement, what is the pattern behind this? etc.
Ozan BAYRAM
The trick is to select every other element (odds specifically) - it is a simple as that. If you want to know more about why they are doing this, that probably depends upon the context (e.g. what exactly are they selecting?).
ternaryOperator
Say you want to seperate the odd and even entries into two columns. You could use the even one for one of them and the odd for the other. Does this help?
Hazior
+4  A: 

It will return all the steps which had odd number of rows.

Nitin Midha
+1  A: 

The COUNT(*) will count all the rows in the database. The % is the modulus character, which will give you the remainder of a division problem. So this is dividing all rows by two and returning those which have a remainder of 1 (meaning an odd number of rows.)

As Erik pointed out, that would not be all the rows, but rather the ones grouped by step, meaning this is all the odd rows per step.

John
It will count the rows, grouped by step.
erikkallen
Thanks, I didn't notice the GROUP BY step.
John
+1  A: 

A reason to do this:

Say you want to seperate the odd and even entries into two columns. You could use the even one for one of them and the odd for the other.

I also put this in a comment but wasn't getting a response.

Hazior
yeah you are right... this can be a reason.
Ozan BAYRAM
+1  A: 

It's impossible for us to answer your question without knowing what the tables are used for.

For a given "Step" it might be that it is required to have an equal amount of "something" and that this will produce a list of elements to be displayed in some interface where this is not the case.

Example: Lets forget "Steps" for a moment and assume this was a table of students and that "Step" was instead "Groups" the students are devided into. A requirement for a group is that there are an even number of students because the students will work in pairs. For an administrative tool you could write a query like this to see a list of groups where this is not true.

Group: Count A, 10 B, 9 C, 17 D, 8 E, 4 F, 5

And the query will return groups B, C, F

Christian Vik
A: 

Thanks to everybody. All of you said the query returns grouped rows that has odd count.

but this is not point! i will continue to inspect this case will and write the reason in the programmer's mind (if i find who write this)

Lessons learned: Programmers must write comments about stupid logic like that...

Ozan BAYRAM
I do not think the logic is wrong, it simply badly coded. If the developer used step instead of * the code would be self explanatory and would not require comments, see my answer for details
kristof
+2  A: 

just test it

declare @t1 table (step char(1))
insert into @t1(step)
select 'a'
union all select 'b'
union all select 'b'
union all select 'c'
union all select 'c'
union all select 'c'
union all select 'd'
union all select 'd'
union all select 'd'
union all select 'd'


select * from @t1
group by step
having count(*)%2 = 1

that will return values of column step that exist add number of times

in this example it will return

'a'
'c'

the select * is confusing here though and I would rather write it as

select step from @t1
group by step
having count(*)%2 = 1

or even for more visibility

select step, count(*) from @t1
group by step
having count(*)%2 = 1
kristof
thanks for detailed sample...
Ozan BAYRAM
you are welcome, I must admit that when I first saw the code I was not sure what was going on, and that was because of the use of "select *"
kristof