using alias coalesce in a condtion : coalesce (max(case when value then value ) as alias

Question

I have for the table ad_params the a crosstab so i made:

coalesce(max(CASE when ad_params.name='model' and ad_params.value='cordova'then ad_params.value end ),NULL) to fix it ,however the condition is ad_params.value='cordova' not working
it's getting me all the result where the model is cordova it is shown else just a blank in the model field so i have to delete all the fields where it is not

  ************************************************************************
  ad_media_id | ad_id  | name | timestamp| regdate | brand | fuel    |model 
   xxxxxxxxxxx   1      jack     xxxx                                cordova
   xxxxxxxxxxx   2      joe      xxxx                                cordova

   ************************************************************************

so i must just have Number 1

coalesce(max(CASE WHEN ad_params.name='regdate' THEN ad_params.value END),NULL) AS regdate,

 select ad_media.ad_media_id
   , ad_media.ad_id
   , ads.name
   , action_states.timestamp
   , max(CASE WHEN ad_params.name = 'regdate' THEN ad_params.value END) 
    AS regdate
   , max(CASE WHEN ad_params.name = 'mileage' THEN ad_params.value END) 
     AS mileage
   , max(CASE WHEN ad_params.name = 'fuel'    THEN ad_params.value END) 
     AS fuel
   , max(CASE WHEN ad_params.name = 'brand'   THEN ad_params.value END) 
     AS brand
   , max(CASE WHEN ad_params.name = 'model'   THEN ad_params.value END) 
     AS model

 from ad_media 
 left join action_states 
 on action_states.ad_id = ad_media.ad_id
 inner join ads 
 on ads.ad_id = action_states.ad_id
 inner join ad_params 
 on  ad_params.ad_id = ad_media.ad_id
 and 
 (
    ad_params.name = 'model' 
    and  ad_params.value = 'cordoba' 
 )


   where action_states.state = 'reg'   
   and action_states.action_id = '1' 
   and action_states.timestamp::DATE BETWEEN '2018-04-17' AND '2018-04-17'
   and ads.category = '2010'   

   group by ad_media.ad_media_id
  , ad_media.ad_id
  , ads.name
  , action_states.timestamp 
  order by ad_media.ad_id;

@JohnLBevan here is the problem thanks for your time :-) could you please review it ? — Medone, Apr 19 '18 at 12:17
You rarely never combine `SELECT DISTINCT` and `GROUP BY`. (GROUP BY returns no duplicates here, so no need to do SELECT DISTINCT.) — jarlh, Apr 19 '18 at 12:28
i see thanks for informing @jarlh , how about making a condtion on `coalesce(max(CASE WHEN ad_params.name='regdate' THEN ad_params.value END),NULL) AS regdate,`any idea please ? — Medone, Apr 19 '18 at 12:32
@Medone; [as mentioned](https://stackoverflow.com/a/49917837/361842) you can replace all `coalesce(max(CASE WHEN ad_params.name='XXX' THEN ad_params.value END),NULL) AS XXX` statements with `max(CASE WHEN ad_params.name='XXX' THEN ad_params.value END) AS XXX`; the two are functionally identical as the coalesce is superfluous — JohnLBevan, Apr 19 '18 at 13:30
I think your question's asking how to filter on just those records where the max value for `redgate` is `2018` when joining to `ad_params`. The issue is that this data is calculated from `ad_params`; so essentially you're asking for a circular reference / paradox... Please could you explain more on what you're trying to achieve rather than how you're trying to achieve this? i.e. I believe this is an [XY Problem](https://meta.stackexchange.com/a/66378/199916). — JohnLBevan, Apr 19 '18 at 13:32
Is your `ad_params` table essentially a way of providing additional columns for your `ad_media` table? i.e. Is the combination of `ad_id` and `name` columns a unique key on `ad_params`? To put it another way; do you want the `max` function, or have you only included it because you needed to (i.e. since you know there will only be 1 result that you're interested in, but the DB has no way to predict that). — JohnLBevan, Apr 19 '18 at 13:42
@JohnLBevan Basically this query is retrieving all the result with a condition from user inputs so for example he will ask the `timestamp` it's ok already done , ,however when it comes to the `ad_params table` it's a` crosstab` so the `name` field in `ad_params` has an` enum value` such as : `fuel , mileage,brand,model` which i have to make them as fields , that is why i did the `max aggregation` and `case when` , the problem is when i have to make a condition on this new fields made i have the problem on the alias for each new fields that is unknown — Medone, Apr 19 '18 at 13:49
Perfect; so you don't care that the max regdate value is 2018; only that the only regdate value is 2018. — JohnLBevan, Apr 19 '18 at 13:52

JohnLBevan · Accepted Answer · 2018-04-19T16:29:12.573

0

Per discussion in comments, because you're expecting each ad_id in ad_params to have a single value per name, we can rewrite your code as below, and easily add the required filter:

select ad_media.ad_media_id
, ad_media.ad_id
, ads.name
, action_states.timestamp
, regdate.value AS regdate
, mileage.value AS mileage
, fuel.value AS fuel
, brand.value AS brand
, model.value AS model

from ad_media 
inner join action_states 
    on action_states.ad_id = ad_media.ad_id
inner join ads 
    on ads.ad_id = action_states.ad_id
left join ad_params as regdate --technically we could do an inner join here since we're using this in the WHERE clause now; but I'll leave as an outer join in case you need more advanced logic...
    on regdate.ad_id = ad_media.ad_id
    and regdate.name = 'regdate' 
left join ad_params as mileage
    on mileage.ad_id = ad_media.ad_id
    and mileage.name = 'mileage' 
left join ad_params as fuel
    on fuel.ad_id = ad_media.ad_id
    and fuel.name = 'fuel' 
left join ad_params as brand
    on brand.ad_id = ad_media.ad_id
    and brand.name = 'brand' 
left join ad_params as model
    on model.ad_id = ad_media.ad_id
    and model.name = 'model' 

where action_states.state = 'reg'   
and action_states.action_id = '1' 
and action_states.timestamp::DATE BETWEEN '2018-04-17' AND '2018-04-17'
and ads.category = '2010'   
and regdate.value = '2018' --your condition 

/* --this is probably no longer needed; if it is instead consider adding a `distinct`
group by ad_media.ad_media_id
, ad_media.ad_id
, ads.name
, action_states.timestamp 
order by ad_media.ad_id;
*/

If you want something more in keeping with your original SQL, the below would also work:

select ad_media.ad_media_id
, ad_media.ad_id
, ads.name
, action_states.timestamp
, max(CASE WHEN ad_params.name = 'regdate' THEN ad_params.value END) AS regdate
, max(CASE WHEN ad_params.name = 'mileage' THEN ad_params.value END) AS mileage
, max(CASE WHEN ad_params.name = 'fuel'    THEN ad_params.value END) AS fuel
, max(CASE WHEN ad_params.name = 'brand'   THEN ad_params.value END) AS brand
, max(CASE WHEN ad_params.name = 'model'   THEN ad_params.value END) AS model

from ad_media 
left join action_states 
    on action_states.ad_id = ad_media.ad_id
inner join ads 
    on ads.ad_id = action_states.ad_id
inner join ad_params --since we expect this filter to remove results, we now need it to be an inner join
    ad_params.ad_id = on ad_media.ad_id
    and 
    (
        ad_params.name != 'regdate' --\_i.e. if the value is regdate we want 2018; if it's not regdate we'll take any value
        or ad_params.value = '2018' --/
    )

where action_states.state = 'reg'   
and action_states.action_id = '1' 
and action_states.timestamp::DATE BETWEEN '2018-04-17' AND '2018-04-17'
and ads.category = '2010'   

group by ad_media.ad_media_id
, ad_media.ad_id
, ads.name
, action_states.timestamp 
order by ad_media.ad_id;

We can rewrite this many other ways too; but to choose the bset we'd need to know more about your schema & data. This post gives a bit of useful related info: https://stackoverflow.com/a/7449213/361842 (though focusses on MSSQL rather than Postgres; but similar ideas apply).

edited Apr 19 '18 at 16:29

answered Apr 19 '18 at 13:56

JohnLBevan

22,735
13
96
178

i think that i didn't explain well :-( to make it easy to understand here is the same schema example https://stackoverflow.com/questions/49793575/turn-the-distinct-value-of-columns-into-a-rows-postgres .So i have to retrieve the age where it is equal 23 with ad_id is checked that is the same in join of course , it 's the same philosophy for my case , hope that is more clear , soory for any bothering – Medone Apr 19 '18 at 14:32
I think either solution above should work (aside from filtering on `age = 23`; as I assume that's an example from your previous question rather than relevant to this one). Did you run the SQL? Did the results look correct? If not, what was the issue? – JohnLBevan Apr 19 '18 at 14:37
@ohnLBevan i updated the problem it's getting me just what i needed the `model` alias =`cordoba` but the `fuel` `mileage` `brand` `regdate` their values are blanked – Medone Apr 19 '18 at 14:46
Are there values in the `ad_params` table which have `ad_id` matching the cordova's `ad_id` value, and with names `fuel`, `mileage`, `brand`, and/or `regdate`? – JohnLBevan Apr 19 '18 at 14:48
yes, totally that 's why there is no primary key so there are gonna be grouped by the ad_id , you may check the problem updated with the result getting and the current query handling with – Medone Apr 19 '18 at 14:54
You've edited your question in such a way that it's hard to see the original question vs your amendments after changing code per answers. When editing questions to add new information (e.g. not just to amend basic formatting / make spelling corrections) it's best to leave the original question as-is, and add an UPDATE heading at the end of the existing question with the additional information; so there's a clear progression visible to anyone seeing the post for the first time / and so updates are obvious to trying to see what's changed. – JohnLBevan Apr 19 '18 at 15:03
The current question shows different results and query to the original; so I assume that's what's changed; but the new query is nothing like either of the solutions above; so isn't really relevant to this discussion (so far as I can tell). Sorry if I'm missing something. – JohnLBevan Apr 19 '18 at 15:05
@ JohnLBevan Sorry, i apologize for that i am just a beginner with using this platform , but i learned a lot from this question how to handle it for the next one , this query is getting me all the fields needed with the condition but the other fields from the ad_params such as `regdate` `fuel` `model` are blanked so i can't figure out how to fix it to be shown thank you for your Collaboration and your Patience – Medone Apr 19 '18 at 15:12
@ JohnLBevan the new query is your query proposed before but i changed it to be adaptable with my case – Medone Apr 19 '18 at 15:14
@Medone what happens when you use the query I provided above? NB: In adapting it you've removed the logic which I'd added. Take some time to read and understand what the above logic does / let me know if after consideration anything doesn't make sense & I can help explain it. – JohnLBevan Apr 19 '18 at 15:20
ad_params.ad_id = on ad_media.ad_id should be like this on ad_params.ad_id = ad_media.ad_id just be sure that i 'am on the right way ?? if it is the case i will read and compare and i let know what i found thank you so much for your time – Medone Apr 19 '18 at 15:26
@ JohnLBevan After reading your query example proposed especially for the `( ad_params.name != 'regdate' --\_i.e. if the value is regdate we want 2018; if it's not regdate we'll take any value or ad_params.value = '2018' --/ )` i am getting all the result all the fields when `name=regdate and value=2018` but if it is not it gives me the result of all fields but the regdate field is blank so what i am looking for is this line result to be deleted – Medone Apr 19 '18 at 15:52
@Medone; ah ok - there is a bug in that second SQL; where there's no regdate defined for a given entry the filter won't be applied... If you use the first SQL provided above, that will work as expected (and should perform better too) – JohnLBevan Apr 19 '18 at 16:03
1

Thank you very much indeed for your collaboration it's working perfectly , just you may add the fields selected `regdate.value, mileage.value,brand.value,model.value` in the `group by` clause – Medone Apr 19 '18 at 16:11
Good point on the group by; in fact; now that we're using this technique that can probably be dropped (since there should be no need to group anything anymore)... – JohnLBevan Apr 19 '18 at 16:29

using alias coalesce in a condtion : coalesce (max(case when value then value ) as alias

1 Answers1

Linked