multiply some records in COUNT(*)

Discussion in 'MySQL' started by pacman031, Dec 11, 2010.

0
  1. #1
    i have query like this:

    SELECT COUNT(*), car_id FROM tb1 
WHERE value IN(1000,3000)
GROUP BY car_id
    PHP:
    this query return number of cars that have value of 1000 or 3000$, is there any way to multiply on 2, cars with 3000$ value ?
    for example if there are 3 of 1000$ car & 2 of 3000$ car, it return: 7.
     
    pacman031, Dec 11, 2010 IP
  2. koko5

    koko5 Active Member

    Messages:
    394
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    70
    #2
    ^^You have to group by value to get count of cars for $1000 and $3000...
    SELECT (COUNT(*)*IF(value=1000,1,2)) AS BLA, 
value FROM tb1 
WHERE value IN(1000,3000) 
GROUP BY value;
    PHP:
    Regards :)

    Edit: 
    SELECT SUM(IF(value=1000,1,2)) AS BLA 
FROM tb1 
WHERE value IN(1000,3000);
    PHP:
     
    Last edited: Dec 11, 2010
    koko5, Dec 11, 2010 IP
  3. iama_gamer

    iama_gamer Active Member

    Messages:
    404
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #3
    You could try this:

    SELECT COUNT(*)*value*0.001, car_id FROM tb1
    WHERE value IN(1000,3000)
    GROUP BY car_id,value
     
    iama_gamer, Dec 14, 2010 IP