Count the changes in column value in sql server

Question

1.44/5 (2 votes)

See more:

plz help me to count this
Ex:
Count the changes in column value in sql server - - - -

S.no        Gps
1              on
2              on
3              on
4              off
5              off
6              on
7              on
8              on
9              on
10              off

i want the result - 3

i want to count column value changes not value ... changement of on to off and off to on

Posted 11-Dec-15 23:35pm

Abhishek Dwivedi Kanpur

Updated 13-Dec-15 23:39pm

v4

Add a Solution

Comments

Tomas Takac 12-Dec-15 5:49am

We don't do your homework for you. What did you try? Where are you stuck?

Abhishek Dwivedi Kanpur 14-Dec-15 1:28am

lolz u all r try to teach me and write alot but u r not able to write only 3 lines...plz improve ur sql buz its not simple count futn... we can do this in seconds ..we cant waste tym to do airtcls...

Select count(*)
From table t1
join table t2
on t2.id = t1.id + 1
where t2.Gps<> t1.Gps

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhishek Dwivedi Kanpur · Accepted Answer · 2015-12-13T23:49:00

Solution 2

SQL

Select count(*)
From table t1
join table t2 
on t2.id = t1.id + 1
where t2.Gps<> t1.Gps

Posted 13-Dec-15 23:49pm

Abhishek Dwivedi Kanpur

Updated 14-Dec-15 18:38pm

v2

Comments

vkvictor 14-Dec-15 7:06am

Gud abhishek its works.

Suvendu Shekhar Giri · Accepted Answer · 2015-12-11T23:57:00

Solution 1

I strongly recommend you to learn the basics of SQL.
It's a very simple stuff to do if you know the very basics as far I understood your question.

Check this extremely simplified tutorials
SQL Tutorial[^]

I'll not give the direct solution, rather will help you to get it done yourself.
Hints for your solution-
SQL WHERE Clause[^]

If you still haven't find the solution, come back here and share what you think you forgot to share in the question.

Hope, you'll take the right direction to the solution :)

Posted 11-Dec-15 23:57pm

Suvendu Shekhar Giri

Comments

[no name] 12-Dec-15 9:27am

Nice advice, a 5.

Suvendu Shekhar Giri 12-Dec-15 11:17am

Thanks :)

Abhishek Dwivedi Kanpur 14-Dec-15 1:28am

lolz u all r try to teach me and write alot but u r not able to write only 3 lines...plz improve ur sql buz we can do this in seconds ..we cant waste tym to do airtcls...

Select count(*)
From table t1
join table t2
on t2.id = t1.id + 1
where t2.Gps<> t1.Gps

Suvendu Shekhar Giri 14-Dec-15 4:13am

Hi my GENIUS friend,
This query will never yield "4" as result as asked by you in the question, rather it will show "3" as output. Am I wrong?

Check this in sqlfiddle-
http://sqlfiddle.com/#!6/9eecb7/5040[^]

I can see, you have modified the question later.

Abhishek Dwivedi Kanpur 14-Dec-15 5:38am

yup dear buz evry1 thinking that m asking simple counting process....so i make it more clear....but just think sir how any guy can ask count() here ?....i think my first update create sum confusion , sory for dat but i want to count changes as i write on subject ... just chill and nxt tym i will try to make my update more clearly . :)

Suvendu Shekhar Giri 14-Dec-15 5:47am

ok.. np.. dear! Post your solution as Answer so that others can refer it, in case they need.