help with query

this is my query
Select
fname
,Case
When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
Else animal
End
from tanimals
Group By fname, animal
create table tanimals
(
fname nvarchar(64),
animal nvarchar(64),
)
insert into tanimals (fname, animal) values ('James', 'cat')
insert into tanimals (fname, animal) values ('James', 'dog')
insert into tanimals (fname, animal) values ('Cal', 'dog')
insert into tanimals (fname, animal) values ('Cal', 'fish')
insert into tanimals (fname, animal) values ('Bret', 'fish')
insert into tanimals (fname, animal) values ('Kate', 'fish')
insert into tanimals (fname, animal) values ('Cal', 'turtle')
I'm trying to produce
fname animals
James 2
Cal 3
Kate fish
Bret fish
if the person have more that one animals to give me the number of
animals, but if they own just one to return the animal that they own.
Thanks
Select
fname
,Case
When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
Else MAX(animal) -- Use aggregate to satisfy Group By
End as Animals
from tanimals
Group By fname -- Not including animal
order by Animals
RLF
<vncntj@.hotmail.com> wrote in message
news:1174415339.746907.287300@.o5g2000hsb.googlegro ups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>
|||vncntj,
I believe this will work for you:
select
fname
, animal AnimalOrCount
from tanimals
where fname in
(
select fname
from tanimals
group by fname
having count(*) = 1
)
union all
select fname
, cast(count(*) as nvarchar(64)) AnimalOrCount
from tanimals
group by fname
having count(*) > 1
-- Bill
<vncntj@.hotmail.com> wrote in message
news:1174415339.746907.287300@.o5g2000hsb.googlegro ups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>
|||It is odd, but when it works, it works a charm. :-)
RLF
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OEGliNyaHHA.4008@.TK2MSFTNGP05.phx.gbl...
> LOL... That is much more clever than my approach. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
>
|||Thanks to everyone. It all works.
You guys rock!!
On Mar 20, 4:38 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> It is odd, but when it works, it works a charm. :-)
> RLF
> "Tibor Karaszi" <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote in
> messagenews:OEGliNyaHHA.4008@.TK2MSFTNGP05.phx.gbl. ..
>
>
>
>
>
>
>
> - Show quoted text -