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