Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jan 2008
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Apply different maths depending on string contents

    EDIT: Having done some more research I have got a little closer to my solution. Please see the second post before replying!
    EDIT 2: Resolved. I'll show the answer below

    Hi all, a bit of a wierd one now. I am trying to limit this to the fewest number of queries (2), but if it's not possible then I can always just use a bit of PHP.

    It's a bit complicated to explain the entire database structure but here goes.

    I have my first query working well, which outputs two columns: tagName (string) and tagRating (float). Let's say this output is:

    Code:
    tagName | tagRating
    'red'   | 4.2
    'blue'  | 3.9
    'silver'| 2.4
    If you hadn't guessed, I'm trying to do a "recommended for you" type query. So that's the first output, and it essentially tells us that the user really likes red cars.

    Now comes the next bit. I want to go back into the database and select all the tagNames that have been added by ALL users. So the table may look like this:

    *NOTE: yes, car 34 has been tagged as both 'blue' and 'red'.
    Code:
    tagID   | userID   | tagName   | carID
    1       | 2        | 'red'     | 34       
    2       | 3        | 'red'     | 45       
    3       | 4        | 'blue'    | 34
    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:

    Code:
    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?
    Last edited by nxzmplty; 12-12-2011 at 07:50 PM.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    if your tables froma above are A
    ,B,C

    Code:
    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.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    New Coder
    Join Date
    Jan 2008
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK so I have just learned about the CASE operator

    Code:
    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?

  • #4
    New Coder
    Join Date
    Jan 2008
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by BubikolRamios View Post
    if your tables froma above are A
    ,B,C

    Code:
    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.

  • #5
    New Coder
    Join Date
    Jan 2008
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by nxzmplty View Post
    Code:
    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!
    Last edited by nxzmplty; 12-12-2011 at 08:04 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •