good day.

Table 1

Table 1 has 2 columns. One column is "ProductId", another is "OrderId". In OrderId, some order is unique ("7", "12") nut some orders are repeated (OrderId "13" repeated 3 times, OrderId "14" repeated 5 times). Now I want to get Table 2 which will indicate the repetition frequency.

I want to know, how many unique entries there, then how many 2 times repeated OrderId there, how many 3 times repeated OrderId there, and so on

Table 2

Now, what will be the SQL query to get Table 2 from Table 1?

SELECT OrderId, COUNT(*) as Repeating_Order_Id
FROM Table 1
GROUP BY OrderId; 

This code does not serve my purpose.

CodePudding user response：

If you want to get the numbers for a given sequence of frequencies you will need a nums table to go along, like this one:

create temporary table nums (i int);
insert into nums VALUES (1),(2),(3),(4),(5);

Then, this should do it:

select i,COALESCE(c2,0) cnt FROM nums LEFT JOIN
 ( select cnt, count(*) c2 FROM 
  ( select ord_id, count(*) cnt FROM tbl group by ord_id) 
  t1 group by cnt ) 
 t2 ON cnt=i 

See demo here: dbfidle.uk

CodePudding user response：

Try :

SELECT OrderId, COUNT(OrderId) as Repeating_Order_Id FROM Table 1 GROUP BY OrderId;

Replace COUNT(*) by COUNT(OrderId)

REF: https://qastack.fr/programming/3841295/sql-using-alias-in-group-by

CodePudding user response：

You can do:

select cnt as repeating_order_id, count(*) as order_number
from (
  select product_id, count(*) as cnt
  from t
  group by product_id
) x
group by cnt