Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: multiply some records in COUNT(*)

    i have query like this:

    Code:
    SELECT COUNT(*), car_id FROM tb1 
    WHERE value IN(1000,3000)
    GROUP BY car_id
    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.
    i know i can use SUM with a little trick, but i wonder if there is any way to do it with count.
    Last edited by bono56; 11-20-10 at 13:33.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a lot depends on the primary key

    what is the primary key of tb1?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    144
    why primary key? i think it not important, suppose something like `car_id`.

    car_id cat_id value
    1 a 1000
    2 a 1000
    3 a 1000
    4 b 3000
    5 b 3000

    there are 3 of cat a & 2 of cat b.
    count return 5, but i want to consider every category b record, double.
    3+2*2=7

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bono56 View Post
    why primary key? i think it not important, suppose something like `car_id`.
    which simply reveals that you are not telling us the correct information

    with car_id in the GROUP BY, your COUNT(*) will be exactly 1

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Do you mean something like:

    Code:
    select sum(case when value = 1000 then 1
                    when value = 3000 then 2
               end)
        , a.car_cnt
        , a.value
        SELECT COUNT(*) as car_cnt
             , value
           FROM tb1 
        WHERE value IN(1000,3000)
        GROUP BY value) as a
    Dave

Posting Permissions

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