This is how my license table look like.. i want to select only row that has atleast one "1".
which means i don't want to select any row that has full of zeros..

Recommended Answers

[CODE]
SELECT *
FROM licence
WHERE F1+F2+F3+F4+F5+F6+F7+F8+A+A1+B+B1+B2+C+C1+C2+V1 > 0
[/CODE]

Jump to Post

All 5 Replies

Use a calculated column that sums all your 0/1 columns. Then you can just add WHERE summed > 0

Use a calculated column that sums all your 0/1 columns. Then you can just add WHERE summed > 0

you mean a calculated colum as an alias right. give me an idea to summ all 0/1..

SUM(column1, column2, ...) or just column1 + column2 + ...

SUM(column1, column2, ...) or just column1 + column2 + ...

SELECT 
sum(F1)+sum(F2)+sum(F3)+sum(F4)+sum(F5)+sum(F6)+sum(F7)+sum(F8)+
sum(A)+sum(A1)+sum(B)+sum(B1)+sum(B2)+sum(C)+sum(C1)+sum(C2)+sum(V1)
as total
FROM `licence` 
WHERE total> 0

the error says unknown column total. my idea is to select rows that atleast have one "1"

SELECT *
FROM `licence` 
WHERE F1+F2+F3+F4+F5+F6+F7+F8+A+A1+B+B1+B2+C+C1+C2+V1 > 0
commented: Perfect answer +1
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.