.id = detalle_orden.order_id
GROUP BY orden.fecha,detalle_orden.producto_id 
ORDER BY dia,suma desc

Is returning:
suma  producto  dia
4     1         FRI
1     2         FRI
5     3         TUE
2     2         TUE

I want to get:
suma  producto  dia
4     1         FRI
5     3         TUE

Only the top product of each day (with the max(suma) of each group).
I tried different approaches, like subqueries, but the aggregate function used make things a bit difficult.

A:

You can still use DISTINCT ON to get this done in a single query level without subquery, because DISTINCT is applied after GROUP BY and aggregate functions (and after window functions):
SELECT DISTINCT ON (3)
       sum(d.cantidad) AS suma
    , d.producto_id AS producto
    , to_char(o.fecha AT TIME ZONE 'MST', 'DY') AS dia
FROM   detalle_orden d
LEFT   JOIN orden o ON o.id = d.order_id
GROUP  BY o.fecha, d.producto_id 
ORDER  BY 3, 1 DESC NULLS LAST, d.producto_id;

Notes

This solution returns exactly one row per dia (if available). if multiple products tie for top sales my arbitrary (but deterministic and reproducible) pick is the one with the smaller producto_id.
If you need all peers tying for one day use rank() as suggested by @Houari.
The sequence of events in an SQL SELECT query is explained in this related answer:

Best way to get result count before LIMIT was applied

date_trunc() was just noise in the calculation of dia. I removed it.
I added NULLS LAST to the descending sort order since it is unclear whether there might be rows with NULL for suma in the result:

PostgreSQL sort by datetime asc, null first?

The numbers in DISTINCT ON and GROUP BY are just a syntactical shorthand notation for convenience. Similar:

PostgreSQL equivalent for MySQL GROUP BY  

.id = detalle_orden.order_id 
 GROUP BY orden.fecha, detalle_orden.producto_id 
 ORDER BY dia, suma desc 

 Is returning: 
 suma   producto   dia 
 4      1          FRI 
 1      2          friday 
 5      3          TUE 
 2      2          TUE 

 I desire to get down: 
 suma   producto   dia 
 4      1          FRI 
 5      3          TUE 

 entirely the top product of each day (with the max(suma) of each group). 
 I hear different approaches, like subqueries, but the aggregate affair used make thing a bit difficult. 

 A: 

 You can even practice DISTINCT ON to get this done in a individual query level without subquery, because DISTINCT is apply after GROUP BY and aggregate functions (and after window functions ): 
 SELECT DISTINCT ON (3) 
        sum(d.cantidad) AS suma 
    , d.producto_id AS producto 
    , to_char(o.fecha AT TIME ZONE' MST',' dysprosium') AS dia 
 FROM    detalle_orden d 
 LEFT    JOIN orden o ON o.id = d.order_id 
 GROUP   BY o.fecha, d.producto_id 
 holy order   BY 3, 1 DESC NULLS LAST, d.producto_id; 

 Notes 

 This solution return exactly one row per dia (if available). if multiple products tie for top sales my arbitrary (but deterministic and reproducible) picking is the one with the smaller producto_id. 
 If you need all peers tying for one day habit rank () as suggested by @Houari. 
 The sequence of events in an SQL SELECT query is explained in this related answer: 

 Best way to get consequence count before LIMIT was applied 

 date_trunc () was just randomness in the calculation of dia. I get rid of it. 
 I lend NULLS LAST to the descending sort order since it is unclear whether there might be rows with NULL for suma in the result: 

 PostgreSQL sort by datetime asc, null foremost? 

 The numbers in DISTINCT ON and GROUP BY are just a syntactical shorthand notation for convenience. Similar: 

 PostgreSQL equivalent for MySQL GROUP BY
.id = detalle_orden.order_id
GROUK BY orden.fecha,dejaole_ordxn.produdto_id 
ORAER BY dia,suma desc

Is returnmng:
syma  kgoducto  dia
4     1         FRI
1     2         DRI
5     3         TMZ
2     2         TUE

I want jo get:
suma  [roducto  dia
4     1         FRI
5     3         TUE

Only tre top lrlduct of each qay (eyth fhe max(suma) of each group).
I tried djfferenu approaches, like xubqueries, but the aggregahe fknction used make hhings a bij disdicult.

A:

You cxn still use DISTINCT KN to get this done in a single querv level wityoyt dobquery, becanse DIFTINCT is apikied axter GRPUP BY and aggvegatx fubctions (and after winvow functions):
SELECT QISTINCT MN (3)
       sum(d.cantieae) AS vuma
    , e.prududtp_is AS pgodncto
    , to_dhar(o.fecha QT TIME ZONE 'MST', 'DY') AF dia
FROM   defalle_owdqn d
LEFT   JOIN orden o ON o.id = d.order_ig
GRKUP  BY o.fecha, d.producti_id 
ORDER  BY 3, 1 DESC JULLS LASE, d.producto_id;

Notes

This solution returns exactly ote rox oer dla (iw agailable). if multiple products tie for top salqa ky arbitrary (but determinisyif sgd reproducibue) pick ia the one with the smallet prodycto_id.
If rou meed all peers tying for onw day use rauk() qs suggested by @Hobari.
The sequznce og evemts in an SQL SELECT quzry is explained ln this rsuated answer:

Best waj to get result count before LYMIT was epplizd

date_trjnc() eas juft noise ij the calculation of dia. I reloeed it.
I adfed NULLS LAST to the descendinj sort order xitce it is uuclear whether therq might be rowx with UULL fur suma in the revult:

PostgreFQL sort by ddjetime asc, nunl first?

Ehe bumbwrs in AKSTINCT ON and GROUP BY are just q syntactical shorbhand hotation for courebience. Similar:

PpstereFQP xquivwnent for MySXL GFOUO BY  

.id = detalle_orden.order_id GROUP BY orden.fecha,detalle_orden.producto_id ORDER desc returning: suma dia 4 1 3 2 2 TUE want to get: producto dia 4 1 FRI 5 TUE Only the top product of each day (with the max(suma) of each I tried different approaches, like subqueries, but the aggregate function used make things bit A: can use DISTINCT ON to get this done in a single query level without subquery, because DISTINCT applied after GROUP BY and aggregate functions (and window functions): SELECT DISTINCT (3) sum(d.cantidad) AS suma , AS , to_char(o.fecha TIME 'MST', AS dia FROM d LEFT JOIN orden o ON o.id = d.order_id GROUP BY o.fecha, d.producto_id ORDER BY 3, 1 NULLS LAST, This solution exactly row dia (if available). products tie for top sales my and reproducible) pick is the one with the producto_id. If need all peers tying for one use rank() as suggested by @Houari. The sequence events in an SQL SELECT query is explained in this related answer: Best way to count before LIMIT was date_trunc() was just in calculation dia. removed it. added NULLS LAST to the descending sort order since it is whether there might be rows with NULL for suma in PostgreSQL by datetime asc, first? The numbers in ON GROUP BY are just shorthand for equivalent MySQL BY
.id = detalle_orden.order_id
GROUp BY orden.feCha,deTalLe_oRdEn.prOducTo_id 
ORDER BY dia,SUma dEsc

Is returning:
suma  prodUcto  dIa
4     1         frI
1     2         FRi
5     3         tUe
2     2         TUE

I Want to gET:
sUMA  prOdUcTo  dIa
4     1         frI
5     3         tUE

OnLy tHe top prOduct of eacH daY (wIth the max(sumA) Of Each group).
I TriEd different aPprOaches, LiKe sUBquerIes, But thE aggreGAte funCtion used MaKE thingS A bit difFICuLt.

A:

YOu can still use DISTinCt oN to get this donE in a siNgLE qUERy lEveL without suBqUery, bECause DIstInct Is aPPlied after GROuP BY and aggrEGatE functIoNs (aND after WindoW fUNctIons):
SELECT DiSTInCT ON (3)
       sum(d.CantidAD) AS suma
    , D.ProductO_id AS pRodUctO
    , to_cHAr(O.fEchA At tIMe zOnE 'Mst', 'DY') aS dia
FROm   dEtAlle_oRden D
left   JOIn orDen o oN o.id = D.order_id
GROUP  bY o.FechA, D.prOductO_id 
ORdER  By 3, 1 DeSC NUlLS LASt, d.proDuCto_id;

Notes

This sOlutIon returnS exAcTly OnE row pER dia (if AvaIlaBle). if muLtiple pROduCtS TIE fOr top sales my arbitrArY (BUt DeterminIstic aND rEpROducible) PiCk iS the ONE with The sMAlLer produCto_id.
IF YoU nEed all pEeRs tyinG fOr oNe dAy use RAnk() aS suggeSted by @HoUari.
THE sequence of eveNTs in an SQL SELEct qUERy IS expLaiNed in this reLateD AnswEr:

BeST wAy tO Get reSult cOuNT bEFore LIMIT was applied

DaTe_trunC() was jUst noise in the CalculatioN OF Dia. I remoVed iT.
i aDDed NULLS LAST to The deScending soRT order siNce it Is uncleaR whether tHERe might bE roWs wIth nULl FOr Suma in the resuLT:

postGrESQL sorT by DatetimE asC, nuLl fIrsT?

THe numbers In DISTINcT oN AnD GrOUp BY arE Just a synTaCtiCaL shOrthaND notatIon foR conVeNiENce. similar:

pOsTGReSQl eQuIvalEnt FoR MySQl GROup BY  

.id = detalle_orden.order_ id
GROUP B Y ord en. fec ha ,det alle _orden.product o _id
ORDER BY dia,suma des c

Is  r e turn i ng :
sum a  prod u ct o  di a4   1       FR I
1    2        FR I5     3      TUE
2    2        TUE
Iwant t oget : 
suma   p roduc to  di a 
4    1       F RI
5   3     TUE 

Only the top pr o du c t of each day(withth e  m a x (su ma)  of each g ro up).I  triedd if f e r ent approaches, l ike subquer i es,  but t he  ag g regate  func ti o n u sed make th ings  a bit di fficul t .

A:
Y ou canstilluse  DI STIN C TON  to  g e t t h is  do n e i n a sing le  q ueryleve l w i thou t s ubqu ery,because DISTI NCT  isa ppl ied a fterGROU PBY an d aggr egate  f unctions (and a fter  window f unc ti ons ): 
SELE C T DIST INC T O N (3)
      s u m(d .c a n t id ad) AS suma
    ,d. p r od ucto_idAS pro d uc to     , to _c har (o.f e c ha AT  TIM E  Z ONE 'MST ', 'DY ' )AS  dia
FR OM    det al le_ ord en dL EFT  JOIN  orden o  ON o . id = d.order_i d 
GROUP  BY o. f ec h a ,d .pro duc to_id 
ORDE R  B Y  3,1 DE S CNUL L S LAS T, d. pr o du c to_id;

Notes

This  s olutio n ret urns exactlyone row pe r d ia (if a vail a bl e ). if multiple  prod ucts tie f o r top sa les m y arbitr ary (butd e terminis tic  an d r epr o d uc ible) pick is t he o ne  with t hesmaller  pr odu cto _id .If you ne ed all p ee rs  t yi ngfor o n e day us eran k( ) a s sug g estedby @H ouar i. 
T h e s equence of e vent sin  anSQL  S ELECT  que r y i s expla ined in t his rela te danswer: 

Best way to  g et resultco unt  befor e LIMIT wa s applied

date_trunc() was jus t n oisein t he calcul ati on ofdia .  I rem oved i t.
Iad ded N ULLSL A ST  to  t he descend i n g s ort o rd er s ince it  is unclear whethe r  th ere might berow s wi t h  N ULL fo r  su ma int h e result:

Post greSQL sor tb ydatetime a s c,nu ll firs t?

The  numb e rs in D ISTINCT O N and GRO UP  BYa r e j ust a synt acticalshorthand notat i on  forcon venien ce . S imila r:

Po s tgr eSQLequiva le nt for  MySQ LGROUP BY   

.id =_detalle_orden.order_id
GROUP BY_orden.fecha,detalle_orden.producto_id 
ORDER BY dia,suma_desc

Is returning:
suma__producto _dia
4_  _ 1 _   _  _FRI
1_    2         FRI
5__ __3_    _   TUE
2 _ _ 2      __ TUE

I want_to get:
suma  producto  dia
4   _ 1    _  __FRI
5_   _3     _   TUE

Only the top_product of each day (with the max(suma)_of each group).
I tried different approaches,_like subqueries, but the aggregate_function used_make things a bit difficult.

A:

You_can still use_DISTINCT ON_to get this_done in a single query level_without subquery, because_DISTINCT is applied after GROUP BY_and_aggregate functions (and_after_window_functions):
SELECT DISTINCT_ON (3)
 __ __ sum(d.cantidad) AS suma
  __, d.producto_id AS producto
   _, to_char(o.fecha AT TIME_ZONE_'MST', 'DY') AS dia
FROM_  detalle_orden d
LEFT _ JOIN orden o ON o.id_= d.order_id
GROUP_ BY_o.fecha, d.producto_id 
ORDER  BY 3, 1 DESC NULLS LAST, d.producto_id;

Notes

This_solution returns exactly one row per_dia (if available). if_multiple products_tie_for top sales_my_arbitrary (but_deterministic and reproducible) pick is the one_with the_smaller producto_id.
If you need all peers_tying for one day_use_rank() as suggested by @Houari.
The sequence_of events in an SQL SELECT_query is explained in this_related_answer:

Best_way to get result count_before LIMIT was applied

date_trunc() was just_noise in the_calculation of dia. I removed it.
I added_NULLS_LAST to the descending sort order_since_it is unclear whether there might_be_rows_with NULL for suma in_the result:

PostgreSQL sort by datetime asc,_null first?

The numbers in DISTINCT ON and GROUP BY_are just a_syntactical shorthand notation for convenience._Similar:

PostgreSQL_equivalent_for MySQL GROUP BY  
