My table design is next
table oc_za_puni_uredjaj
sif_company (integer - primary, value for company)
sif_device (integer - primary, value for device)
sif_nos (integer - primary, value for customer)
sif_mp (integer - primary, value for customer's place)
rbr (integer - primary, value for customer's place item)
ocitano (integer - 0 - empty, 1 - full)
datum (date - date of the record)
area (text - name of area of customer)
Example of data in database is next
sif_company | sif_device | sifnos | sif_mp | rbr | ocitano | datum | area |
---------------------------------------------------------------------------
1 | 1 | 1 | 1 | 1 | 1 |... |abcd
1 | 1 | 1 | 2 | 1 | 0 |... |abcd
1 | 1 | 2 | 1 | 1 | 1 |... |abc
1 | 1 | 1 | 2 | 1 | 1 |... |abcd
1 | 1 | 1 | 2 | 3 | 1 |... |abcd
My existing query which works (for some cases) is next
SELECT area as naselje,
count(*) total,
sum(case when ocitano = 1 then 1 else 0 end) ocitano
FROM `oc_za_puni_uredjaj`
WHERE sif_company = 1 group by 1
The result looks like
naselje | total | ocitano
------------------------------------
abc | 1 | 1
abcd | 4 | 3
The problem is if i have same data for 2 different devices (sif_device). I could have data in table like below
sif_company | sif_device | sifnos | sif_mp | rbr | ocitano | datum | area |
---------------------------------------------------------------------------
1 | 1 | 1 | 1 | 1 | 1 |... |abcd
1 | 2 | 1 | 1 | 1 | 0 |... |abcd
1 | 1 | 2 | 1 | 1 | 1 |... |abcd
1 | 2 | 2 | 1 | 1 | 1 |... |abcd
1 | 1 | 3 | 1 | 1 | 1 |... |abc
My desired output for that data should look like
naselje | total | ocitano
------------------------------------
abc | 1 | 1
abcd | 2 | 2
So if i have in table row with (sif_nos, sif_mp, rbr) for different device (sif_device) then i have cases:
- if only one of them has
ocitano = 1then for that area i have to increment ocitano in output by 1 - if there is several rows with
ocitano = 1then i have to increment ocitano in output by 1 - if all of them got
ocitano = 0then i don't increment ocitano in output
EDIT
Any help would be nice
Aucun commentaire:
Enregistrer un commentaire