CODEWITHSUNDEEP
sqlgoogle-bigquery
ASP YOK
ASP YOK

Reputation: 131

Count multiple row values in SQL

I have the following table:

Pic

It counts for the number and year if a car was used or not based on the month. A would now write a query for a specific year like 

> where year = 2018 (Lets pretend it now February)

The result I need is from 2018 backwards a car is not used. For example for 2018 it would be 3 because from now on (FEB) till Dez.2017 there are 3 following 0's. If a 1 appears -> stop counting. If I'm interested in 2017 

> where year = 1.2017

it does of course not matter which month I'm in currently because 2017 is already over so it would count 1 (3 in the pic is obviously wrong)

Upvotes: 0

Views: 81

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

One method is a giant case expression

select t.*,
       (case when greatest(jan, feb, . . . , dec) = 0 then 12
             when greatest(jan, feb, . . . , nov) = 0 then 11
             . . .
             when jan = 0 then 1
             else 0
        end) as result_i_need
from t;

Upvotes: 0

Erick Stone
Erick Stone

Reputation: 809

select count(*) from TABLE where year <= 2018 and sum(monthA + monthB + monthC + ...) <= 3;

Upvotes: 2

Related Questions