MySQL - How to count and sum in a single query

Question

I would like to make a count and a sum on differents values in a single query. In fact i've got a Table of subscriptions. I count all subscriptions and i count all validated subscriptions. It works. But ... i would like to sum an another value of a subscription named CAR. I would like to sum all CAR and all validated CAR.

So my table looks like (in fact it's bigger than this) :

ID | NUM | CAR | STATE | DATE_SIGNATURE

So this is my try :

SELECT
        COUNT(s.num) as total_bs,
        SUM(CASE WHEN s.state = '400' THEN 1 ELSE 0 END) as total_validated_bs,
        SUM(s.car) as total_car,
        SUM(CASE WHEN s.state = '400' THEN 1 ELSE 0 END) as total_validated_car,
        DATE_FORMAT( s.date_signature, '%u' ) week_number,
        DATE_ADD(s.date_signature, INTERVAL(1-DAYOFWEEK(s.date_signature)) +1 DAY) week_start
        FROM subscription as s
        GROUP BY DATE_FORMAT( s.date_signature, '%u' )
        LIMIT 0,12

And this is my result :

Total subscription : 276

Total validated subscriptions : 170

Total CAR : 4378760

Total Validated CAR : 170

All is correct except validated car. It must be a bigger number but lower than CAR. IN Fact i would like to have the SUM of all CAR from subscriptions that are validated.

It's possible to make it in a single query ? I saw some solutions with a SELECT in another SELECT but i find this one not really elegant. But i don't know if it's efficient.

Thanks everybody :)

The SUM code is the same for total_validated_bs and total_validated_car. I don't know when a car is validated but obviously there is something wrong here. :) — user3849602
– user3849602, Commented Feb 12, 2015 at 17:04

logsv · Accepted Answer · 2015-02-12 17:04:00Z

3

In Your query 
        SUM(CASE WHEN s.state = '400' THEN 1 ELSE 0 END) as total_validated_bs,
        SUM(CASE WHEN s.state = '400' THEN 1 ELSE 0 END) as total_validated_car
both are same  total_validated_bs and total_validated_car
so it will return same result for both.
you should go with different condition for total_validated_car.
Do this
        SUM(CASE WHEN s.state = '400' THEN s.car ELSE 0 END) as total_validated_car

answered Feb 12, 2015 at 17:04

logsv

5546 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thomas Montagnoni Over a year ago

I know that i've got two times the same condition. But i can't go with an another condition. state = 400 means the subscription is validated. So if i want to sum all car from validated subscription i must wheck if the subscription (in this case s.state) is validated. I'm wrong ?

logsv Over a year ago

run This query you will get result. SELECT COUNT(s.num) as total_bs, SUM(CASE WHEN s.state = '400' THEN 1 ELSE 0 END) as total_validated_bs, SUM(s.car) as total_car, SUM(CASE WHEN s.state = '400' THEN s.car ELSE 0 END) as total_validated_car, DATE_FORMAT( s.date_signature, '%u' ) week_number, DATE_ADD(s.date_signature, INTERVAL(1-DAYOFWEEK(s.date_signature)) +1 DAY) week_start FROM subscription as s GROUP BY DATE_FORMAT( s.date_signature, '%u' ) LIMIT 0,12

Thomas Montagnoni Over a year ago

Well ... in fact the solution was easy but i didn't saw it ! Thank you dude ! You made my day :))

Collectives™ on Stack Overflow

MySQL - How to count and sum in a single query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related