Hello,

I would like to know if there is a better solution for my view.

In the context, i have two table, one, is chart, a chart contains a
difficulty, the other table contains records, a record reference a
chart and a playerid and contains a ranking.

I wish to create a view that store for each player id the amount of A,
B, C, D, F, S and X rank per difficulty.

here my probably not optimal solution:
CREATE VIEW playerranks AS
SELECT record.player, chart.difficulty as level, count(record.player)
as rankx, 0 as ranks, 0 as ranka, 0 as rankb, 0 as rankc, 0 as rankd,
0 as rankf FROM record join chart on chart=chart.id WHERE
record.rank='X' GROUP BY record.player,chart.difficulty
UNION
SELECT record.player, chart.difficulty as level, 0 as rankx,
count(record.player) as ranks, 0 as ranka, 0 as rankb, 0 as rankc, 0
as rankd, 0 as rankf FROM record join chart on chart=chart.id WHERE
record.rank='S' GROUP BY record.player,chart.difficulty
UNION
SELECT record.player, chart.difficulty as level, 0 as rankx, 0 as
ranks, count(record.player) as ranka, 0 as rankb, 0 as rankc, 0 as
rankd, 0 as rankf FROM record join chart on chart=chart.id WHERE
record.rank='A' GROUP BY record.player,chart.difficulty
UNION
SELECT record.player, chart.difficulty as level, 0 as rankx, 0 as
ranks, 0 as ranka, count(record.player) as rankb, 0 as rankc, 0 as
rankd, 0 as rankf FROM record join chart on chart=chart.id WHERE
record.rank='B' GROUP BY record.player,chart.difficulty
UNION
SELECT record.player, chart.difficulty as level, 0 as rankx, 0 as
ranks, 0 as ranka, 0 as rankb, count(record.player) as rankc, 0 as
rankd, 0 as rankf FROM record join chart on chart=chart.id WHERE
record.rank='C' GROUP BY record.player,chart.difficulty
UNION
SELECT record.player, chart.difficulty as level, 0 as rankx, 0 as
ranks, 0 as ranka, 0 as rankb, 0 as rankc, count(record.player) as
rankd, 0 as rankf FROM record join chart on chart=chart.id WHERE
record.rank='D' GROUP BY record.player,chart.difficulty
UNION
SELECT record.player, chart.difficulty as level, 0 as rankx, 0 as
ranks, 0 as ranka, 0 as rankb, 0 as rankc, 0 as rankd,
count(record.player) as rankf FROM record join chart on chart=chart.id
WHERE record.rank='F' GROUP BY record.player,chart.difficulty;



Any advice will be appreciate

On 2011-10-31 17:30, galopin wrote:
CREATE VIEW playerranks AS
SELECT
record.player, chart.difficulty as level,
count(case when record.rank='X' then record.player end) as rankx,
count(case when record.rank='S' then record.player end) as ranks,
count(case when record.rank='A' then record.player end) as ranka,
count(case when record.rank='B' then record.player end) as rankb,
count(case when record.rank='C' then record.player end) as rankc,
count(case when record.rank='D' then record.player end) as rankd,
count(case when record.rank='F' then record.player end) as rankf
FROM record
join chart
on chart=chart.id
WHERE record.rank in ('X', ... )
GROUP BY record.player,chart.difficulty
record.rank='X' GROUP BY record.player,chart.difficulty


/Lennart

On 2011-10-31 23:28, Lennart Jonsson wrote:
Another option:

CREATE VIEW playerranks AS
SELECT
record.player, chart.difficulty as level, record.rank, count(*) as ...
FROM record
join chart
on chart=chart.id
GROUP BY record.player, chart.difficulty, record.rank

and then handle the rest in the presentation layer.


/Lennart

On 1 nov, 05:15, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Thank you, there is a real gap between the versions