Help with a Query

I have been designing a website for our fishing club and I need help
with one of my queries because I'm not to good at it yet.
The table has FishID, Angler, RecordDate, Species, Length, Photo for
columns.
The species can only be - Muskie, Northern, Walleye, Catfish, Crappie
What I want is display the data like this
Angler Muskie_total Northern_total Walleye_total Catfish_total
Crappie_Total Longest_fish Total_points
All species except Muskie are worth 1 point, Muskie are worth 5,
longest fish is worth 5
So it didn't take long for this to get over my head.
Anybody want to give me a hand at this, leagues start in 2 weeks!!!
Thanks,
MartyOn Apr 30, 5:52 am, Marty <mcoon...@.gmail.com> wrote:
> I have been designing a website for our fishing club and I need help
> with one of my queries because I'm not to good at it yet.
> The table has FishID, Angler, RecordDate, Species, Length, Photo for
> columns.
> The species can only be - Muskie, Northern, Walleye, Catfish, Crappie
> What I want is display the data like this
> Angler Muskie_total Northern_total Walleye_total Catfish_total
> Crappie_Total Longest_fish Total_points
> All species except Muskie are worth 1 point, Muskie are worth 5,
> longest fish is worth 5
> So it didn't take long for this to get over my head.
> Anybody want to give me a hand at this, leagues start in 2 weeks!!!
> Thanks,
> Marty
Try something on these lines
SELECT Angler ,
SUM(CASE WHEN Species = 'Muskie' THEN 5 END ) AS Muskie_total ,
SUM(CASE WHEN Species = 'Northern' THEN 1 END ) AS
Northern_total ,
SUM(CASE WHEN Species = 'Walleye' THEN 1 END ) AS Walleye_total ,
SUM(CASE WHEN Species = 'Catfish' THEN 1 END ) AS Catfish_total,
SUM(CASE WHEN Species = 'Crappie' THEN 1 END ) AS Crappie_Total ,
SUM(CASE WHEN Species = 'Longest_fish ' THEN 5 END ) AS
Longest_fish_Total
FROM Fish
Group by Angler|||On Apr 30, 12:55 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Apr 30, 5:52 am,Marty<mcoon...@.gmail.com> wrote:
>
>
> > I have been designing a website for our fishing club and I need help
> > with one of my queries because I'm not to good at it yet.
> > The table has FishID, Angler, RecordDate, Species, Length, Photo for
> > columns.
> > The species can only be - Muskie, Northern, Walleye, Catfish, Crappie
> > What I want is display the data like this
> > Angler Muskie_total Northern_total Walleye_total Catfish_total
> > Crappie_Total Longest_fish Total_points
> > All species except Muskie are worth 1 point, Muskie are worth 5,
> > longest fish is worth 5
> > So it didn't take long for this to get over my head.
> > Anybody want to give me a hand at this, leagues start in 2 weeks!!!
> > Thanks,
> >Marty
> Try something on these lines
> SELECT Angler ,
> SUM(CASE WHEN Species = 'Muskie' THEN 5 END ) AS Muskie_total ,
> SUM(CASE WHEN Species = 'Northern' THEN 1 END ) AS
> Northern_total ,
> SUM(CASE WHEN Species = 'Walleye' THEN 1 END ) AS Walleye_total ,
> SUM(CASE WHEN Species = 'Catfish' THEN 1 END ) AS Catfish_total,
> SUM(CASE WHEN Species = 'Crappie' THEN 1 END ) AS Crappie_Total ,
> SUM(CASE WHEN Species = 'Longest_fish ' THEN 5 END ) AS
> Longest_fish_Total
> FROM Fish
> Group by Angler- Hide quoted text -
> - Show quoted text -
Thanks!!!sql