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.
ThanksSelect
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.googlegroups.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
>|||Below are two options. Second requires SQL Server 2005:
SELECT fname, CAST(COUNT(*) AS varchar(30)) AS animals
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1
UNION ALL
SELECT fname, animal
FROM tanimals
WHERE fname NOT IN
(
SELECT fname
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1
);
WITH ganimals AS
(SELECT fname, CAST(COUNT(*) AS varchar(30)) AS animals
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1)
SELECT fname, animal
FROM tanimals
WHERE fname NOT IN (SELECT fname FROM ganimals)
UNION
SELECT fname, animals FROM ganimals
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<vncntj@.hotmail.com> wrote in message news:1174415339.746907.287300@.o5g2000hsb.googlegroups.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.googlegroups.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
>|||> Else MAX(animal) -- Use aggregate to satisfy Group By
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...
> 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.googlegroups.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...
>> Else MAX(animal) -- Use aggregate to satisfy Group By
> 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...
>> 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.googlegroups.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
>>
>|||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...
>
> >> Else MAX(animal) -- Use aggregate to satisfy Group By
> > 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" <russellfie...@.nomail.com> wrote in message
> >news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
> >> 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
> >> <vnc...@.hotmail.com> wrote in message
> >>news:1174415339.746907.287300@.o5g2000hsb.googlegroups.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- Hide quoted text -
> - Show quoted text -