Is it possible for the value of one field to dependent on the value of another?
The context is a table with two fields:
Rank
Score
The rank can be 1, 2, 3, 4 or 5.
The score needs to be 10 (for a rank of 1), 6 (for a rank of 2), 3 (for a rank of 3), 2(for a rank of 4) or 1 (for a rank of 5).
So basically if the rank field is updated to 1, can it be set up so that the score field automatically updates to 10 etc?
Thanks.
you shouldn’t do this
you can always compute the score from the rank in any SELECT
or, you can declare a view with the computed score, and query that instead of the table
How would I do that? I know I can use things like COUNT to compute things, and looking up RANK use that to compute rankings if a field has values for a score.
But how would I use anything in a SELECT to display scores based on the value of a rank field?
SELECT rank
, CASE WHEN rank=1 THEN 10
WHEN rank=2 THEN 6
WHEN rank=3 THEN 3
WHEN rank=4 THEN 2
WHEN rank=5 THEN 1
ELSE 0 END AS score
FROM daTable
Thank you - that’s perfect!
If I have two ranking fields, rank1 and rank2, I can use:
SELECT
rank1
, CASE WHEN rank1=1 THEN 10
WHEN rank1=2 THEN 6
WHEN rank1=3 THEN 3
WHEN rank1=4 THEN 2
WHEN rank1=5 THEN 1
ELSE 0 END AS score1,
rank2
, CASE WHEN rank2=1 THEN 10
WHEN rank2=2 THEN 6
WHEN rank2=3 THEN 3
WHEN rank2=4 THEN 2
WHEN rank2=5 THEN 1
ELSE 0 END AS score2,
FROM daTable
Can I then output the sum of score1 + score2?
I know I can use SUM to output the sum of all values in a field for different records, but what would the syntax be here?
I have been trying things like:
SUM(score1 + score2) AS TotalScore,
But it doesn’t like it because score1 and score2 are not fields in the table.
[quote=“johngordon, post:5, topic:98415, full:true”] Thank you - that’s perfect! [/quote] yours isn’t – it has a “dangling comma” in front of the FROM keyword
use a subquery
SELECT rank1
, score1
, rank2
, score2
, score1 + score2 AS total_score
FROM ( SELECT rank1
, CASE WHEN rank1=1 THEN 10
WHEN rank1=2 THEN 6
WHEN rank1=3 THEN 3
WHEN rank1=4 THEN 2
WHEN rank1=5 THEN 1
ELSE 0 END AS score1
, rank2
, CASE WHEN rank2=1 THEN 10
WHEN rank2=2 THEN 6
WHEN rank2=3 THEN 3
WHEN rank2=4 THEN 2
WHEN rank2=5 THEN 1
ELSE 0 END AS score2
FROM daTable
) AS subquery
My existing query is quite complex - I’ve tried to work the above in, but without any luck.
The original looks like:
SELECT ScoreID, COUNT(ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID, lodges.Lodge, Country, lodges.CountryID, categoriesFull.CategoryID, nominations.NominationID, Year, Comments, CategoryShort, CountryShort, rank1, rank2
FROM lodges INNER JOIN countries ON lodges.CountryID = countries.CountryID
INNER JOIN scores ON lodges.LodgeID = scores.LodgeID
INNER JOIN nominations ON lodges.LodgeID=nominations.LodgeID
INNER JOIN categoriesFull ON nominations.CategoriesFullID = categoriesFull.CategoryID AND scores.categoryID = categoriesFull.CategoryID
INNER JOIN categoriesShort ON categoriesFull.CategoryID = categoriesShort.CategoryShortID
WHERE lodges.CountryID = 1 AND scores.CategoryID = 1 AND Year = '2015' AND Nominee = 'Y'
GROUP BY Lodge, NominationID
So I’ve tried:
SELECT ScoreID, COUNT(ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID, lodges.Lodge, Country, lodges.CountryID, categoriesFull.CategoryID, nominations.NominationID, Year, Comments, CategoryShort, CountryShort,
, rank1
, score1
, rank2
, score2
FROM ( SELECT rank1
, CASE WHEN rank1=1 THEN 10
WHEN rank1=2 THEN 6
WHEN rank1=3 THEN 3
WHEN rank1=4 THEN 2
WHEN rank1=5 THEN 1
ELSE 0 END AS score1
, CG_rank
, CASE WHEN rank2=1 THEN 10
WHEN rank2=2 THEN 6
WHEN rank2=3 THEN 3
WHEN rank2=4 THEN 2
WHEN rank2=5 THEN 1
ELSE 0 END AS score2
FROM lodges INNER JOIN countries ON lodges.CountryID = countries.CountryID
INNER JOIN scores ON lodges.LodgeID = scores.LodgeID
INNER JOIN nominations ON lodges.LodgeID=nominations.LodgeID
INNER JOIN categoriesFull ON nominations.CategoriesFullID = categoriesFull.CategoryID AND scores.categoryID = categoriesFull.CategoryID
INNER JOIN categoriesShort ON categoriesFull.CategoryID = categoriesShort.CategoryShortID
WHERE lodges.CountryID = 1 AND scores.CategoryID = 1 AND Year = '2015' AND Nominee = 'Y'
GROUP BY Lodge, NominationID
) AS subquery
Which I don’t think is right?
[quote=“johngordon, post:7, topic:98415, full:true”]Which I don’t think is right?[/quote]you are correct, it isn’t right
the “subquery” derived table is the only table in your FROM clause, and consequently, you may only reference columns that are actually selected in the SELECT list of the subquery – you may not reference columns such as lodges.LodgeID in the outer query
if you know which table the rank column comes from, put the subquery at that level
OK - so the sub query part can only reference the table containing the rank columns, which is nominations.
But I still don’t get the syntax to fit the sub query part in with the existing query:
SELECT ScoreID, COUNT(ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID, lodges.Lodge, Country, lodges.CountryID, categoriesFull.CategoryID, nominations.NominationID, Year, Comments, CategoryShort, CountryShort,
, rank1,
, rank2
, score1
, score2
FROM ( SELECT rank1
, CASE WHEN rank1=1 THEN 10
WHEN rank1=2 THEN 6
WHEN rank1=3 THEN 3
WHEN rank1=4 THEN 2
WHEN rank1=5 THEN 1
ELSE 0 END AS score1
, CG_rank
, CASE WHEN rank2=1 THEN 10
WHEN rank2=2 THEN 6
WHEN rank2=3 THEN 3
WHEN rank2=4 THEN 2
WHEN rank2=5 THEN 1
ELSE 0 END AS score1
FROM nominations) AS subquery
FROM lodges INNER JOIN countries ON lodges.CountryID = countries.CountryID
INNER JOIN scores ON lodges.LodgeID = scores.LodgeID
INNER JOIN nominations ON lodges.LodgeID=nominations.LodgeID
INNER JOIN categoriesFull ON nominations.CategoriesFullID = categoriesFull.CategoryID AND scores.categoryID = categoriesFull.CategoryID
INNER JOIN categoriesShort ON categoriesFull.CategoryID = categoriesShort.CategoryShortID
WHERE lodges.CountryID = 1 AND scores.CategoryID = 1 AND Year = '2015' AND Nominee = 'Y'
GROUP BY Lodge, NominationID
please identify the tables to which each of the columns belong that are marked in red below
SELECT [color=red]ScoreID[/color], COUNT([color=red]ScoreID[/color]) as scoreCount, AVG([color=red]Score[/color]) as AverageScore, lodges.LodgeID, lodges.Lodge, [color=red]Country[/color], lodges.CountryID, categoriesFull.CategoryID, nominations.NominationID, [color=red]Year[/color], [color=red]Comments[/color],[color=red]ScoreID[/color] [color=red]CategoryShort[/color], [color=red]CountryShort[/color],
, rank1,
, rank2
, score1
, score2
FROM ( SELECT rank1
, CASE WHEN rank1=1 THEN 10
WHEN rank1=2 THEN 6
WHEN rank1=3 THEN 3
WHEN rank1=4 THEN 2
WHEN rank1=5 THEN 1
ELSE 0 END AS score1
, CG_rank
, CASE WHEN rank2=1 THEN 10
WHEN rank2=2 THEN 6
WHEN rank2=3 THEN 3
WHEN rank2=4 THEN 2
WHEN rank2=5 THEN 1
ELSE 0 END AS score1
FROM nominations) AS subquery
FROM lodges INNER JOIN countries ON lodges.CountryID = countries.CountryID
INNER JOIN scores ON lodges.LodgeID = scores.LodgeID
INNER JOIN nominations ON lodges.LodgeID=nominations.LodgeID
INNER JOIN categoriesFull ON nominations.CategoriesFullID = categoriesFull.CategoryID AND scores.categoryID = categoriesFull.CategoryID
INNER JOIN categoriesShort ON categoriesFull.CategoryID = categoriesShort.CategoryShortID
WHERE lodges.CountryID = 1 AND scores.CategoryID = 1 AND [color=red]Year [/color]= '2015' AND [color=red]Nominee [/color]= 'Y'
GROUP BY [color=red]Lodge[/color], [color=red]NominationID[/color]damned stupid new forum software
but at least i think you know which columns need clarification, yes?
ScoreID - scores
COUNT ScoreID as scoreCount - scores (basically counts the number of records in the query)
Score - not a field, but used here to output the average score
Country - countries
Year - nominations
Nominee - nominations
Comments - scores
CategoryShort - categoriesShort
CountryShort - countries
Just to clarify the whole thing. It might not be the best structure, but there are two scoring things going on.
There is a scores table which records scores from 1-10 that people have been making when visiting the website. So any given lodge can have multiple votes in any particular category.
And there is a nominations table, whereby any given lodge can only have one nomination in any particular category. So if a lodge has had any votes in a particular category, it gets a nomination. And it is this table which stores the ranks we’ve been looking at.
The idea now is that some industry judges can view all the results and rank any lodges from 1-5. Its slightly odd in that these rankings may not reflect the 1-5 based on the average score in the scores table.
And yes - the language used is terrible, and back to front, but its partly historical, and partly because they changed things around this year in a way I don’t personally think makes a whole lot of sense, but it is what it is.
So for example:
Table - lodges
LodgeID (PK), Lodge
1, Lodge A
2, Lodge B
3, Lodge C
etc
Table - categoriesFull
CategoryID (PK), Category
1, Best Value
2, Best Cuisine
etc
Table - scores
ScoreID (PK), Score, LodgeID (FK), CategoryID (FK)
1, 5, 1, 2
2, 7, 1, 2
3, 9, 1, 2
etc
So Lodge A has received 3 votes (scoreCount) in the Best Cuisine category, with an average score of 7 (AverageScore)
That Lodge will also have a row in the nominations table:
NominationID (PK), LodgeID (FK), CategoriesFullID (FK), Rank1, Rank2, Year, Nominee, Finalist, RunnerUp, Winner
1, 1, 2, 1, 2, 2015, Y, N, N, N
So Lodge A has a nomination for Best Cuisine, and has been given one rank (Rank1) of 1 (worth 10 points) and on rank (rank2) of 2 (worth 6 points).
Hope that makes some sense - it doesn’t help that the system / process the site owners want to run is probably more complicated than it really needs to be.
SELECT countries.CountryID
, countries.Country
, countries.CountryShort
, lodges.LodgeID
, lodges.Lodge
, scrs.scoreCount
, scrs.AverageScore
, categoriesFull.CategoryID
, categoriesShort.CategoryShort
, noms.NominationID
, noms.Year
, noms.Comments
, noms.rank1
, noms.rank2
, noms.score1
, noms.score2
FROM countries
INNER
JOIN lodges
ON lodges.CountryID = countries.CountryID
INNER
JOIN ( SELECT LodgeID
, categoryID
, COUNT(ScoreID) as scoreCount
, AVG(Score) as AverageScore
FROM scores
WHERE CategoryID = 1
GROUP
BY LodgeID
, categoryID
) AS scrs
ON scrs.LodgeID = lodges.LodgeID
INNER
JOIN ( SELECT CategoriesFullID
, LodgeID
, Year
, Comments
, rank1
, CASE WHEN rank1=1 THEN 10
WHEN rank1=2 THEN 6
WHEN rank1=3 THEN 3
WHEN rank1=4 THEN 2
WHEN rank1=5 THEN 1
ELSE 0 END AS score1
, CG_rank
, CASE WHEN rank2=1 THEN 10
WHEN rank2=2 THEN 6
WHEN rank2=3 THEN 3
WHEN rank2=4 THEN 2
WHEN rank2=5 THEN 1
ELSE 0 END AS score1
FROM nominations
WHERE Year = '2015'
AND Nominee = 'Y'
) AS noms
ON noms.LodgeID = lodges.LodgeID
INNER
JOIN categoriesFull
ON categoriesFull.CategoryID = noms.CategoriesFullID
AND categoriesFull.CategoryID = scores.categoryID
INNER
JOIN categoriesShort
ON categoriesShort.CategoryShortID = categoriesFull.CategoryID
WHERE countries.CountryID = 1 Thanks for this - looks like it’s gotten more involved than I anticipated, so I appreciate you taking the time to help out.
That was drawing a blank when the original was returning records. Can I just check something - are ‘noms’ and ‘scrs’ your shorthand for ‘nominations’ and ‘scores’ or should they be ‘noms’ and ‘scrs’ as the aliases?
[quote]Can I just check something - are ‘noms’ and ‘scrs’ your shorthand for ‘nominations’ and ‘scores’ or should they be ‘noms’ and ‘scrs’ as the aliases?[/quote]they are aliases, and very important
OK - that rules that out.
I had an error about score1 being a duplicate field, so assumed the second score1 should have been score2?
And I wasn’t sure which bit calculated score1 + score2?
Or should they both be score1, which is effectively the total score after each instance of CASE?
Sorry for the questions, just trying to understand a bit what the code is doing.
Looking at it again, I’m getting errors about the following columns being unknown:
Comments
noms.NominationID
scores.categoryID
I took out Comments as its not being used.
I added NominationID here:
INNER
JOIN ( SELECT CategoriesFullID
, LodgeID
, Year
, AY_rank
, NominationID
And I thought scores.categoryID needed to be scores.CategoryID, but still showing as unknown.
I haven’t changed that, so with those changes, the query currently looks like:
SELECT countries.CountryID
, countries.Country
, countries.CountryShort
, lodges.LodgeID
, lodges.Lodge
, scrs.scoreCount
, scrs.AverageScore
, categoriesFull.CategoryID
, categoriesShort.CategoryShort
, noms.NominationID
, noms.Year
, noms.AY_rank
, noms.CG_rank
, noms.score1
, noms.score2
FROM countries
INNER
JOIN lodges
ON lodges.CountryID = countries.CountryID
INNER
JOIN ( SELECT LodgeID
, categoryID
, COUNT(ScoreID) as scoreCount
, AVG(Score) as AverageScore
FROM scores
WHERE CategoryID = 7
GROUP
BY LodgeID
, categoryID
) AS scrs
ON scrs.LodgeID = lodges.LodgeID
INNER
JOIN ( SELECT CategoriesFullID
, LodgeID
, NominationID
, Year
, AY_rank
, CASE WHEN AY_rank=1 THEN 10
WHEN AY_rank=2 THEN 6
WHEN AY_rank=3 THEN 3
WHEN AY_rank=4 THEN 2
WHEN AY_rank=5 THEN 1
ELSE 0 END AS score1
, CG_rank
, CASE WHEN CG_rank=1 THEN 10
WHEN CG_rank=2 THEN 6
WHEN CG_rank=3 THEN 3
WHEN CG_rank=4 THEN 2
WHEN CG_rank=5 THEN 1
ELSE 0 END AS score2
FROM nominations
WHERE Year = '2015'
AND Nominee = 'Y'
) AS noms
ON noms.LodgeID = lodges.LodgeID
INNER
JOIN categoriesFull
ON categoriesFull.CategoryID = noms.CategoriesFullID
AND categoriesFull.CategoryID = scores.categoryID
INNER
JOIN categoriesShort
ON categoriesShort.CategoryShortID = categoriesFull.CategoryID
WHERE countries.CountryID = 8
With the error: Unknown column ‘scores.categoryID’ in ‘on clause’
sorry, it should be scrs.categoryid
also, the score1/score1 was obviously just a typo
Thanks - that’s all looking present and correct again now.
After trying a few places for it, I have the line for the total_score here:
, noms.AY_country_rank
, noms.CG_rank
**, score1 + score2 as total_score**
FROM countries
INNER
JOIN lodges
ON lodges.CountryID = countries.CountryID
Which looks like it works - does that look like the right line in the right place?
OK, I now have this which is all working great:
SELECT countries.CountryID
, countries.Country
, countries.CountryShort
, lodges.LodgeID
, lodges.Lodge
, lodges.2015_Awards_Participant
, scrs.scoreCount
, scrs.AverageScore
, categoriesFull.CategoryID
, categoriesShort.CategoryShort
, noms.NominationID
, noms.Year
, noms.AY_rank
, noms.CG_rank
, AY_score + CG_score as JudgesScore
FROM countries
INNER JOIN lodges
ON lodges.CountryID = countries.CountryID
INNER JOIN (SELECT LodgeID
, categoryID
, COUNT(ScoreID) as scoreCount
, AVG(Score) as AverageScore
FROM scores
WHERE CategoryID = 8
GROUP BY LodgeID, categoryID)
AS scrs
ON scrs.LodgeID = lodges.LodgeID
INNER JOIN ( SELECT CategoriesFullID
, LodgeID
, NominationID
, Year
, AY_finalist
, AY_rank
, AY_country_winner
, AY_country_rank
, CASE
WHEN AY_rank=1 THEN 10
WHEN AY_rank=2 THEN 6
WHEN AY_rank=3 THEN 3
WHEN AY_rank=4 THEN 2
WHEN AY_rank=5 THEN 1
ELSE 0 END AS AY_score
, CG_finalist
, CG_rank
, CG_country_winner
, CG_country_rank
, CASE
WHEN CG_rank=1 THEN 10
WHEN CG_rank=2 THEN 6
WHEN CG_rank=3 THEN 3
WHEN CG_rank=4 THEN 2
WHEN CG_rank=5 THEN 1
ELSE 0 END AS CG_score
FROM nominations
WHERE Year = '2015' AND Nominee = 'Y')
AS noms
ON noms.LodgeID = lodges.LodgeID
INNER JOIN categoriesFull
ON categoriesFull.CategoryID = noms.CategoriesFullID
AND categoriesFull.CategoryID = scrs.categoryID
INNER JOIN categoriesShort
ON categoriesShort.CategoryShortID = categoriesFull.CategoryID
WHERE countries.CountryID = 7
The absolutely last thing that would be useful is to output the sum of:
AverageScore + JudgesScore (what was total_score). So that would give a combined total taking into account the main voting and the judge’s rankings.
I tried adding
AverageScore + JudgesScore AS TotalScore
But it didn’t like it.
Can that be worked in without too much hassle?