Now here's the tricky bit. I want to group the cars by ID, but create another temporary column called (for example) 'tagScore'. If tagName = 'red', tagScore would be 4.2 as per the original output. This is put into the query using the PHP.

Then, once grouped we would have another temporary column in an output like this:

carID | carScore
34 | 8.1 //(4.2+39)
45 | 3.9

So yeah, a bit long winded but would appreciate some answers!

Is it even possible?
Is it worth it or should I just go ahead and use 5 or so queries?
EDIT: Simple question: Is it possible, in a SELECT statement, to create a temporary column with values depending on the contents of a string?

BubikolRamios

12-12-2011, 07:03 PM

if your tables froma above are A
,B,C

select B.carId, sum(A.tagRating) as carScore
from A left join B on A.tagName = B.tagName
group by B.carID

Something like that, for start, would give you C.

nxzmplty

12-12-2011, 07:32 PM

OK so I have just learned about the CASE operator :)

SELECT carID,
CASE tagName
WHEN 'red' THEN 4.2
WHEN 'blue' THEN 3.9
WHEN 'silver' THEN 2.4
END as tagScore
FROM tags

This seems to do the first bit. However, is there now a way to take tagScore as a column, group by carID, and get the SUM(tagScore)? I currently get an error that tagScore is not a valid column. How can I reuse this temporary column?

nxzmplty

12-12-2011, 07:35 PM

if your tables froma above are A
,B,C

select B.carId, sum(A.tagRating) as carScore
from A left join B on A.tagName = B.tagName
group by B.carID

Something like that, for start, would give you C.

Thanks for the reply. However, tagRating is calculated on-the-fly in the first query and is not available for a join.

nxzmplty

12-12-2011, 07:52 PM

SELECT sum(
CASE tagName
WHEN 'red'
THEN 4.2
WHEN 'blue'
THEN 3.9
WHEN 'silver'
THEN 2.4
END ) AS carScore, carID
FROM tags
GROUP BY carID

Well then, that turned out to be a lot easier than I made it out to be. Hope this helps somebody at some point!