multiply some records in COUNT(*)

bono56 · #1 (**permalink**) 11-20-10, 10:45

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.

r937 · #2 (**permalink**) 11-20-10, 12:05

a lot depends on the primary key

what is the primary key of tb1?

bono56 · #3 (**permalink**) 11-20-10, 12:37

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

r937 · #4 (**permalink**) 11-20-10, 12:44

Quote:

Originally Posted by bono56

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

dav1mo · #5 (**permalink**) 11-22-10, 11:17

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

Internet Services

Web Development

Digital Marketing

Consumer Tech