Get specific rows from group by clause in PostgreSQL

» mysql » Get specific rows from group by clause in PostgreSQL

By : Joey

Date : November 19 2020, 03:59 PM

I wish did fix the issue. If I've a table like below, ,
I guess you're using PostgreSQL Database if so then try this :

code :

create temp table temp_com as select pid,bid,fl,fq,fo from  com limit 0;

with cte as (
            select c1.pid,c1.bid,c1.fl,c1.fq,c1.fo 
            from com c1 inner join com c2 using (pid) 
            where c2.fo=1
)
insert into temp_com (pid,bid) (select pid,bid from cte);

with cte as(
           select c1.pid,c1.bid,c1.fl,c1.fq,c1.fo 
           from com c1 inner join com c2 using (pid) 
           where c2.fo=1 
)
update temp_com a 
set fl= cte.fl,
    fq=cte.fq,
    fo=cte.fo 
from cte 
where a.pid=cte.pid and cte.fo=1; -- gets the row have `fo=1`

pid   bid   fl fq  fo
----------------------
3823  2341  3   1   1
3823  2340  3   1   1
3823  2350  3   1   1
3474  2350  1   2   1
3474  2340  1   2   1

Share :

How to Update multiple rows having specific count with Group by clause?

By : Abdul Sharifi

Date : March 29 2020, 07:55 AM

Any of those help I want to update multiple rows that have specific count number (count(*)=2) with number 40. Consider the following example: , God... Finally, we came up with working query:

code :

UPDATE test1 SET tst = 40 WHERE EXISTS (SELECT day FROM (SELECT day from test1 ) 
AS tmpb WHERE test1.day = tmpb.day GROUP BY day HAVING COUNT(*) = 2)

How to: Group items, retrieve one (random) row data of group and count rows in each group at one SQL clause

By : wesley chen

Date : March 29 2020, 07:55 AM

it should still fix some issue I have simple table in database where are columns error_id, error_group_id and message. I want to show listing in UI which shows Message and occurrences of each error group. , You can add a COUNT in your current query:

code :

SELECT message, error_group_id, cnt
  FROM (SELECT error_id,
               message,
               error_group_id,
               rank() OVER (PARTITION BY error_group_id ORDER BY error_id) rank,
               count(*) OVER (PARTITION BY error_group_id) cnt
          FROM cc_errors)
 WHERE rank <= 1;

How to add numbers to grouped rows in postgresql group by clause

By : user3708657

Date : March 29 2020, 07:55 AM

will help you With help from this question and its answers:

code :

SELECT gid, capt, row_number() OVER (PARTITION BY capt ORDER BY gid) AS rnum
FROM your_table_here ORDER BY gid;

Group by clause in mySQL and postgreSQL, why the error in postgreSQL?

By : Simo

Date : March 29 2020, 07:55 AM

With these it helps You need to use AGGREGATE FUNCTION:

code :

SELECT col2, MIN(col3) AS col3, MIN(col1) AS col1
FROM the_table 
GROUP BY col2;

SELECT o.custid, c.name, MAX(o.payment)
FROM orders AS o
JOIN customers AS c
  ON o.custid = c.custid
GROUP BY o.custid;

PostgreSQL - Group by filter out specific rows

By : Hanna Afiah

Date : March 29 2020, 07:55 AM

help you fix your problem Your question is asking about an exact match for the threshold. This is basically a cumulative sum:

code :

select cct.*
from (select ch.customer_id, ch.amount,
             sum(ch.amount) over (partition by ch.customer_id order by post_date) as running_amount,
             t.threshold_amount
      from charges ch join
           customers c
           on ch.customer_id = c.id join
           threshholds t
           on c.threshold_id = t.id
     ) cct
where running_amount = threshold_amount;