mardi 14 juin 2016

Conditional case in SQL query

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 = 1 then for that area i have to increment ocitano in output by 1
  • if there is several rows with ocitano = 1 then i have to increment ocitano in output by 1
  • if all of them got ocitano = 0 then i don't increment ocitano in output

EDIT

SQL Fiddle

Any help would be nice

Aucun commentaire:

Enregistrer un commentaire