Count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-20 : 23:58:55

Hi, how can I count for the where condition only.

select a,b,c,count(totalPeople)
from ...
where a='UK' and b='US'
group by a,b,c

I only want those from UK to US totalPeople but my query result count those from US to UK and UK to US as well.

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-05-21 : 00:02:20

please add condition

like

select a,b,c,count(totalPeople)
from ...
/*where a='UK' and b='US' commented by nextaxtion */
group by a,b,c
having a='UK' and b='US'

pls check and confirm..

challenge everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 00:03:15

what is the condition for UK to US ?
and
what is the condition for US to UK ?

please post some sample data and expected result

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-21 : 00:09:05

i want the result to be like this:

a b totalPeople
UK US 5

Currently it give me those result which is like this:

a b totalPeople
UK US 10

the totalPeople which is 10 came from:

a b totalPeople
UK US 5
US UK 5

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 00:11:22

can you show us your full query ?

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-21 : 00:13:43

select go + '-' + back as journey,JourneyTag,count(totalPeople)
from tableName
where go='UK' and back='US'
group by go,back,journeyTag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 00:20:04

how about some sample data and expected result ?

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-21 : 00:25:27

select go + '-' + back as journey,JourneyTag,count(totalPeople)
from tableName
where go='UK' and back='US'
group by go,back,journeyTag

journey journeyTag TotalPeople
UK-US AH800 10

The TotalPeople should be 5 because when I run

Select go + '-' + back as journey from tableName
where go='UK' and back='US'

journey journeyTag
UK-US 5

and this

Select go + '-' + back as journey from tableName
where go='US' and back='UK'

journey journeyTag
US-UK 5

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 00:38:40

quote:
The TotalPeople should be 5 because when I run

Select go + '-' + back as journey from tableName
where go='UK' and back='US'

journey journeyTag
UK-US 5

the result does not matches your query. How many records do you get from that query ?

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-21 : 00:54:33

That is why I said it should get 5 instead of 10.

Currently it count the return journey.

How can I only count US-UK.

I already include the WHERE condition but it doesn't work.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 00:59:29

i mean your query is


Select go + '-' + back as journey from tableName
where go='UK' and back='US'

and this is the result of that query ?


journey journeyTag
UK-US 5

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-21 : 02:23:42

Yup..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 02:47:43

You are not really making sense.

In the result there is a value 5, i assumed that is JourneyTag ? By the way, I don't see any column "JourneyTag" specified in that query. Where does this comes from ?

This Query
"Select go + '-' + back as journey from tableName
where go='UK' and back='US'"

only return 1 record ?

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-21 : 02:54:42

This Query:
"Select go + '-' + back as journey from tableName
where go='UK' and back='US'"

Return 5 times because there is 5 people tie to one journeyTag.

Journey JourneyTag
UK-US AH800
UK-US AH800
UK-US AH800
UK-US AH800
UK-US AH800

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-21 : 03:05:17

I only want to count those in my where condition.

But it seems like it count everything.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 03:16:04

quote:
Originally posted by peace

This Query:
"Select go + '-' + back as journey from tableName
where go='UK' and back='US'"

Return 5 times because there is 5 people tie to one journeyTag.

Journey JourneyTag
UK-US AH800
UK-US AH800
UK-US AH800
UK-US AH800
UK-US AH800

You are still not giving is the actual query. From the result, i can see that your query should be


Select go + '-' + back as journey, JourneyTag from tableName
where go='UK' and back='US'

now run this and tell us what is the result


Select go + '-' + back as journey, count(*)
from  tableName
where go='UK' and back='US'
group by go + '-' + back

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-21 : 03:25:48

Yes, thats right i missed out the JourneyTag in select statement.

I run your query:

Journey Total
UK-US 5

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 03:33:55

now run this query.


Select go + '-' + back as journey, JourneyTag, count(*) as Total
from   tableName
where  go='UK' and back='US'
group by go + '-' + back , JourneyTag

OR

Select go + '-' + back as journey, JourneyTag, count(*) as Total
from   tableName
where  go='UK' and back='US'
group by go , back , JourneyTag

It should give you


Journey JourneyTag Total
UK-US   AH800      5

and that is what you wanted ?

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-21 : 03:35:33

when i run without the count and also without the where condition it return this:

Journey JourneyTag
UK-US AH800
UK-US AH800
UK-US AH800
UK-US AH800
US-UK AH800
US-UK AH800
US-UK AH800
US-UK AH800

i only want to count those from UK-US but my query seems like return the whole count of this JourneyTag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 03:39:17

quote:
Originally posted by peace

when i run without the count and also without the where condition it return this:

Journey JourneyTag
UK-US AH800
UK-US AH800
UK-US AH800
UK-US AH800
US-UK AH800
US-UK AH800
US-UK AH800
US-UK AH800

i only want to count those from UK-US but my query seems like return the whole count of this JourneyTag

if you only wanted to count those from UK-US, then why are you removing the where condition in the query ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources