what is count(*) % 2 = 1

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?

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).

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

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

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

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.

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.

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

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...