CODEWITHSUNDEEP
sqlsqlite
manutenfruits
manutenfruits

Reputation: 1577

SQLite - check if any field has a 1 on each column

I have the following query:

SELECT c.danhoEstetico, c.danhoDirecto
FROM conceptos c
INNER JOIN materialesconceptos mc ON mc.idConcepto = c.idConcepto
INNER JOIN materiales m ON m.idMaterial = mc.idMaterial
WHERE m.idMaterial IN (4,11,11)

Which yields the following result (for example):

danhoEstetico | danhoDirecto
1             | 0
1             | 0
0             | 0
0             | 0

I need to make a query that gives me if any of the items in each column has a 1, like an OR among all the fields in the same column, with an output like:

danhoEstetico | danhoDirecto
1             | 0

I have tried: 

SELECT
SUM(CASE c.danhoEstetico WHEN c.danhoEstetico=1 THEN 1 ELSE 0 END) AS de,
SUM(CASE c.danhoDirecto WHEN c.danhoDirecto=1 THEN 1 ELSE 0 END) AS dd
FROM conceptos c INNER JOIN materialesconceptos mc ON mc.idConcepto = c.idConcepto
INNER JOIN materiales m ON m.idMaterial = mc.idMaterial
WHERE m.idMaterial IN (4,11,11)

Which, for some reason, yields:

danhoEstetico | danhoDirecto
4             | 4

Any hint on this?

Upvotes: 0

Views: 126

Answers (3)

dan1111
dan1111

Reputation: 6566

Edit: I misread the question. This solution checks that every row in a column is 1, rather than any row. I recommend CL's answer, but I will leave this answer here because it explains a problem with the CASE statement in the question.

You should be using CASE without a base expression:

SELECT
SUM(CASE WHEN c.danhoEstetico=1 THEN 1 ELSE 0 END) AS de,
SUM(CASE WHEN c.danhoDirecto=1 THEN 1 ELSE 0 END) AS dd
FROM conceptos c INNER JOIN materialesconceptos mc ON 
    mc.idConcepto = c.idConcepto
INNER JOIN materiales m ON m.idMaterial = mc.idMaterial
WHERE m.idMaterial IN (4,11,11)

From the documentation:

In a CASE with a base expression, the base expression is evaluated just once and the result is compared against the evaluation of each WHEN expression from left to right. The result of the CASE expression is the evaluation of the THEN expression that corresponds to the first WHEN expression for which the comparison is true...

In your statement as written, the case statement always evaluated to 1 when the column was 1 and 0 when the column was 0. Thus, they always matched, and the sum was always the number of rows.

Update: now how do you actually turn this into the result you want? Here is one way:

SELECT
SUM(CASE WHEN c.danhoEstetico=1 THEN 1 ELSE 0 END) / SUM(1) AS de,
SUM(CASE WHEN c.danhoDirecto=1 THEN 1 ELSE 0 END) / SUM(1)  AS dd
FROM conceptos c INNER JOIN materialesconceptos mc ON 
    mc.idConcepto = c.idConcepto
INNER JOIN materiales m ON m.idMaterial = mc.idMaterial
WHERE m.idMaterial IN (4,11,11)

This takes advantage of integer arithmetic. The division will result in a value < 1 if not all rows in a column are one, and a value less than one will show as zero.

Upvotes: 1

CL.
CL.

Reputation: 180080

To get the largest value in each column, use MAX instead of SUM.

Upvotes: 1

podiluska
podiluska

Reputation: 51494

select distinct 
       c.danhoEstetico, c.danhoDirecto
 FROM conceptos c
 INNER JOIN materialesconceptos mc ON mc.idConcepto = c.idConcepto
 INNER JOIN materiales m ON m.idMaterial = mc.idMaterial
where 1 in (c.danhoEstetico , c.danhoDirecto)

Upvotes: 0

Related Questions