CODEWITHSUNDEEP
sqlrelational-databasefirebird
zac
zac

Reputation: 4898

How I should write this SQL

Using Firebird 3.0 I have these tables

enter image description here

The model_types table has some types like Type1, type2, type3... and I need get a result like this:

Type     All    UFZ   Tempered
=================================
Type1     45     23      12
Type2     0      0       0
Type3     2      0       0

So I wrote this SQL:

select 
    sum((window.win_width * window.win_height) / 144),
    model_types.model_type_description
from model_types
   left outer join models on (model_types.model_type_id = models.model_type_id)
   inner join series_model on (models.model_id = series_model.model_id)
   inner join window on (series_model.sm_id = window.sm_id)
 where (quote_id = 122) and (window.ufz = 0) and (window.tempered = 0)
 group by model_types.model_type_description

The problem this SQL give me only "type1" if other types are zeros so how I list all types from model_types table even if they are zeros ?

Another question: in this SQL the where condition :

(window.ufz = 0) and (window.tempered = 0)

represent the "All" column while to get UFZ I need to use these conditions:

(window.ufz = 1) and (window.tempered = 0)

and to get Tempered I need to use this conditions:

(window.ufz = 0) and (window.tempered = 1)

So how I display all the 3 results (All, UFZ, Tempered) each one in separate column ?

Upvotes: 0

Views: 66

Answers (2)

ddmmaaxx
ddmmaaxx

Reputation: 1

The CASE construct has an ELSE part where you may add a default value for the case that your WHEN part is not validated. I think this will complete the missing data of your query result.

Upvotes: 0

zac
zac

Reputation: 4898

After many testing I found that I can use Case but dont know if my SQL is correct or not also it does not display all model types if they are zeros.

select 
    sum(CASE WHEN (window.ufz = 0) and (window.tempered = 0)
      THEN ((window.win_width * window.win_height) / 144) END) AS models_all,

    sum(CASE WHEN (window.ufz = 1) and (window.tempered = 0)
      THEN ((window.win_width * window.win_height) / 144) END) AS models_ufz,

    sum(CASE WHEN (window.ufz = 0) and (window.tempered = 1)
      THEN ((window.win_width * window.win_height) / 144) END) AS models_temp,

    model_types.model_type_description
from model_types
   left outer join models on (model_types.model_type_id = models.model_type_id)
   inner join series_model on (models.model_id = series_model.model_id)
   inner join window on (series_model.sm_id = window.sm_id)
 where (quote_id = 122)
 group by model_types.model_type_description

Upvotes: 0

Related Questions