CODEWITHSUNDEEP
phpmysqlsqlcountsum
Thomas Montagnoni
Thomas Montagnoni

Reputation: 468

MySQL - How to count and sum in a single query

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 :)

Upvotes: 0

Views: 184

Answers (1)

logsv
logsv

Reputation: 544

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

Upvotes: 3

Related Questions