Group by With count

Hi,
Iam using a count along with a group by condition. (Eg., Select count(col1),
col1 from table1 where col1 = <value> group by col1)
If I run the query and if no matching records if found the result doesnt sho
w anything. Why is this so?. But if I give
count(column) without the group by condition then a single record is fetched
based on no of records..
I not able to really see why the first one is not returning any records if t
he condition does not match.
Thanx in advance
regards
MaheshBasically, when the condition does not match, there is nothing is count (it
is a empty result set), which causes nothing to be displayed.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Mahesh" <anonymous@.discussions.microsoft.com> wrote in message
news:0E67C7CE-7744-41D3-B733-17E6BF42E9E4@.microsoft.com...

quote:

---

> Hi,
> Iam using a count along with a group by condition. (Eg., Select

---

count(col1),col1 from table1 where col1 = <value> group by col1)

quote:

---

> If I run the query and if no matching records if found the result doesnt

---

show anything. Why is this so?. But if I give

quote:

---

> count(column) without the group by condition then a single record is

---

fetched based on no of records..

quote:

---

> I not able to really see why the first one is not returning any records if

---

the condition does not match.

quote:

---

>
> Thanx in advance
> regards
> Mahesh
>

---

|||If you want to see the count and the value of Col1 then you can do so like
this:
SELECT COUNT(*), <value>
FROM Table1
WHERE col1 = <value>
David Portas
--
Please reply only to the newsgroup
--

Group by With count

Hi,
Iam using a count along with a group by condition. (Eg., Select count(col1),col1 from table1 where col1 = <value> group by col1)
If I run the query and if no matching records if found the result doesnt show anything. Why is this so?. But if I give
count(column) without the group by condition then a single record is fetched based on no of records..
I not able to really see why the first one is not returning any records if the condition does not match.
Thanx in advance
regards
MaheshBasically, when the condition does not match, there is nothing is count (it
is a empty result set), which causes nothing to be displayed.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Mahesh" <anonymous@.discussions.microsoft.com> wrote in message
news:0E67C7CE-7744-41D3-B733-17E6BF42E9E4@.microsoft.com...
> Hi,
> Iam using a count along with a group by condition. (Eg., Select
count(col1),col1 from table1 where col1 = <value> group by col1)
> If I run the query and if no matching records if found the result doesnt
show anything. Why is this so?. But if I give
> count(column) without the group by condition then a single record is
fetched based on no of records..
> I not able to really see why the first one is not returning any records if
the condition does not match.
>
> Thanx in advance
> regards
> Mahesh
>|||If you want to see the count and the value of Col1 then you can do so like
this:
SELECT COUNT(*), <value>
FROM Table1
WHERE col1 = <value>
--
David Portas
--
Please reply only to the newsgroup
--

Group by

create table my_table (
col1 char10,
col2 char10,
col3 char 10)
select col1, count(*)
from my_table
group by col1
--Now, from this group by results, I want to eliminate all the records where
col2 is Null. Can I accomplished this in the same group by query above?
Remember, I want to eliminate the Nulls after the group by operation been
done, NOT before.
Thank you.
SandraCan you show some sample data and sample output you're looking for? Your
description makes no sense, given the query. The query counts the number of
rows for each distinct value of Col1 -- you're not doing anything with Col2
there, so how can you eliminate rows based on it, after getting those
counts?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sandra" <Sandra@.discussions.microsoft.com> wrote in message
news:41A9E56B-40D9-4505-9AF0-9852B0E9718B@.microsoft.com...
> create table my_table (
> col1 char10,
> col2 char10,
> col3 char 10)
> select col1, count(*)
> from my_table
> group by col1
> --Now, from this group by results, I want to eliminate all the records
> where
> col2 is Null. Can I accomplished this in the same group by query above?
> Remember, I want to eliminate the Nulls after the group by operation been
> done, NOT before.
> Thank you.
> Sandra|||You can compare count(*) to count(col2).
You must modify below query.
select col1, count(col2)
from my_table
group by col1
HAVING Count(Col2) <> 0
"Sandra"?? ??? ??:

> create table my_table (
> col1 char10,
> col2 char10,
> col3 char 10)
> select col1, count(*)
> from my_table
> group by col1
> --Now, from this group by results, I want to eliminate all the records whe
re
> col2 is Null. Can I accomplished this in the same group by query above?
> Remember, I want to eliminate the Nulls after the group by operation been
> done, NOT before.
> Thank you.
> Sandra|||There is no col2 in the results'Anyways I think this was what you wanted.
Let me know.
select col1 ,count(*)
from my_table
group by col1
having count(*) = sum(case when col2 is null then 0 else 1 end)|||If I understand correctly, you want to get a count grouped by col1 where at
least one row has a value in Col2?
If none of the rows for a given Col1 have a value in Col2, then you don't
want them returned?
select col1, count(*), count(Col2)
from my_table
group by col1
having count(Col2) > 0
"Sandra" <Sandra@.discussions.microsoft.com> wrote in message
news:41A9E56B-40D9-4505-9AF0-9852B0E9718B@.microsoft.com...
> create table my_table (
> col1 char10,
> col2 char10,
> col3 char 10)
> select col1, count(*)
> from my_table
> group by col1
> --Now, from this group by results, I want to eliminate all the records
where
> col2 is Null. Can I accomplished this in the same group by query above?
> Remember, I want to eliminate the Nulls after the group by operation been
> done, NOT before.
> Thank you.
> Sandra

Group By

create table my_table (
col1 char10,
col2 char10,
col3 char 10)
select col1, count(*)
from my_table
group by col1
--Now, from this group by results, I want to eliminate all the records where
col2 is Null. Can I accomplished this in the same group by query above?
Remember, I want to eliminate the Nulls after the group by operation been
done, NOT before.
Thank you.
SandraSandra,
the request makes no sence to me. Pls provide test data and expected
results|||Please post your question only once.