Group and count by another columns value

Question

I have a table like below:

CREATE TABLE public.test_table
(
    "ID" serial PRIMARY KEY NOT NULL,
    "CID" integer NOT NULL,
    "SEG" integer NOT NULL,
    "DDN" character varying(3) NOT NULL
)

and data looks like this:

ID  CID SEG DDN
1   1   1   "711"
2   1   2   "800"
3   1   3   "124"
4   2   1   "711"
5   3   1   "711"
6   3   2   "802"
7   4   1   "799"
8   5   1   "799"
9   5   2   "804"
10  6   1   "799"

I need to group these data by CID column and get column counts depends on DDN columns first values but counts must give me two different information, if it's more than 1 or not.

I'm really sorry if couldn't explains clearly. Let me show you what I need..

DDN END TRA
711 1   2
799 2   1

As you can see, DDN:711 has 1 record of single count (ID:4). This is END column. But 2 times has multiple SEG count (ID:1to3 and ID:5to6). This is TRA column.

I can not be sure what column should be in group clause!

My solution:

Just found a solution like below

WITH x AS (
    SELECT
        (SELECT t1."DDN" FROM public.test_table AS t1
         WHERE t1."CID"=t."CID" AND t1."SEG"=1) AS ddn,
        COUNT("CID") AS seg_count       
    FROM public.test_table AS t
    GROUP BY "CID"
)

SELECT ddn, COUNT(seg_count) AS "TOTAL",    
    SUM(CASE WHEN x.seg_count=1 THEN 1 ELSE 0 END) as "END",
    SUM(CASE WHEN x.seg_count>1 THEN 1 ELSE 0 END) as "TRA"
FROM x
GROUP BY ddn;

You have to give us a better explaination of what you want. I did not understand how you counted END and TRA in the example. — Akli REGUIG, Sep 11 '18 at 12:17
Sorry, again! If you look at the first 3 record, CID values are same and there are 3 record means I should count as TRA (TRAnsferred). Now lets look at 4th record, it's CID value is 2 and its single record, that means I should count that as END (ENDed). — James De Souza, Sep 11 '18 at 12:30
Little more explanations; ID:1to3 has same CID and first DDN is 711. I should count this TRA for 711. ID:4 has single CID and first DDN is 711. I should count this END for 711. ID:5to6 has same CID and first DDN is 711. I should count this TRA for 711. ID:7 has single CID and first DDN is 799. I should count this END for 799. ID:8to9 has same CID and first DDN is 799. I should count this TRA for 799. ID:10 has single CID and first DDN is 799. I should count this END for 799. — James De Souza, Sep 11 '18 at 12:31
Please do not post additional information as comments [edit] your question instead. — , Sep 11 '18 at 12:58
i see that you found a solution of your own. i also proposed you one as an answer. — Akli REGUIG, Sep 11 '18 at 13:20

Erwin Brandstetter · Accepted Answer · 2021-01-12T17:14:31.937

Equivalent, faster query:

SELECT "DDN"
     , COUNT(*) AS "TOTAL"
     , COUNT(*) FILTER (WHERE seg_count = 1) AS "END"
     , COUNT(*) FILTER (WHERE seg_count > 1) AS "TRA"
FROM  (
   SELECT DISTINCT ON ("CID")
         "DDN"          -- assuming min "SEG" is always 1
       , COUNT(*) OVER (PARTITION BY "CID") AS seg_count
   FROM   test_table
   ORDER  BY "CID", "SEG"
   ) sub
GROUP  BY "DDN";

db<>fiddle here

Notes

CTEs are typically slower and should only be used where needed in Postgres.

This is equivalent to the query in the question assuming that the minimum "SEG" per "CID" is always 1 - since this query returns the row with the minimum "SEG" while your query returns the one with "SEG" = 1. Typically, you would want the "first" segment and my query implements this requirement more reliably, but that's not clear from the question.

COUNT(*) is slightly faster than COUNT(column) and equivalent while not involving NULL values (applicable here). Related:

PostgreSQL: running count of rows for a query 'by minute'

About DISTINCT ON:

Select first row in each GROUP BY group?

The aggregate FILTER syntax requires Postgres 9.4+:

Conditional SQL count

@James: I added one more improvement. – Erwin Brandstetter Sep 11 '18 at 14:42 — Erwin Brandstetter, Sep 11 '18 at 14:42

score 0 · Answer 2 · answered Sep 11 '18 at 13:18

Here is the solution i propose, the query can be simplified i guess.

CREATE TABLE test_table
(
    ID serial PRIMARY KEY NOT NULL,
    CID integer NOT NULL,
    SEG integer NOT NULL,
    DDN character varying(3) NOT NULL
);



insert into test_table(CID,SEG,DDN)
values
(   1,   1,   '711'),
(   1,   2,   '800'),
(   1,   3,   '124'),
(   2,   1,   '711'),
(   3,   1,   '711'),
(   3,   2,   '802'),
(   4,   1,   '799'),
(   5,   1,   '799'),
(   5,   2,   '804'),
(   6,   1,   '799');


with summary as (with ddn_t as (select cid,ddn,row_number() OVER( PARTITION BY cid)from test_table)
select a.cid,count(distinct a.ddn),b.ddn
 from ddn_t a 
 join ddn_t b on b.cid=a.cid and b.row_number=1
group by a.cid, b.ddn)
select ddn,
sum (case when count >1 then 1 else 0 end) as TRA,
sum (case when count = 1 then 1 else 0 end) as END
from summary
group by ddn;

Group and count by another columns value

My solution:

2 Answers2

Notes