Select count with nonaggregated columns-CodePudding

With MySQL,I have a table where I need select the rows with diferents values in price column with your counting.

Table T1

id name   cat   col1 col2 ...coln  price
1  A      cat1   1    1       1     20
2  A      cat1   1    1       1     20
3  A      cat1   1    1       1     20
4  A      cat1   1    1       1     10
5  A      cat1   1    1       1     20
6  A      cat1   1    1       1     20
7  A      cat1   1    1       1     20

I need a result set as:

id name   cat   col1 col2 ...coln  price  count_of_rows_with_same_prices
1  A      cat1   1    1       1     20           6
4  A      cat1   1    1       1     10           1

I have used something as:

select  price,T1.*,count(*) as count_of_rows_with_same_prices
from T1
group by 1;

But, I need disable "ONLY_FULL_GROUP_BY" because a have nonaggreated columns in my select list.

How can I do that without disable "ONLY_FULL_GROUP_BY"?

CodePudding user response：

actually you could use a query like below

SELECT 
id,name,cat, price,  count_of_rows_with_same_prices
from 
(
SELECT *, count(id) over (partition by price) as count_of_rows_with_same_prices, 
row_number() over (partition by price order by id asc) as r
from T1
)T 
where r=1

CodePudding user response：

You can do this with two window functions:

MIN, working on the id
COUNT, working on the price

Here's the query:

SELECT DISTINCT
    MIN(id) OVER(
        PARTITION BY name, cat, price 
        ORDER BY price
    )                   AS id,
    name,
    cat,
    col1,
    col2,

    ....

    colN,
    COUNT(price) OVER(
        PARTITION BY name, cat, price 
        ORDER BY price
    )                   AS count_of_rows_with_same_prices
FROM 
    tab_t1
ORDER BY 
    id

You can find an SQL fiddle here.

CodePudding user response：

First you want to get the count of entries by price, so write that out:

 SELECT price, count(*) as rc FROM T1 GROUP BY price

Next you can use that as a subquery in your query:

SELECT t1.id, t1.name, t1.cat, t1.col1, t1.col2, t1.coln, t1.price, t2.rc
FROM T1 t1
    INNER JOIN (SELECT price, count(*) as rc FROM T1 GROUP BY price) t2
        ON t1.price = t2.price
WHERE id in (1,4)

If you are on a newer version of mysql that supports window function you could also do something like:

SELECT *
FROM 
    (
       SELECT id, name, cat, col1, col2, coln, price
           ,COUNT(*) OVER (PARTITION BY price) as count_of_rows_with_same_prices
       FROM T1
    )
WHERE id in (1, 4)

Also, don't disable ONLY_FULL_GROUP_BY. That thing is set to TRUE for a reason. You will only write broken incorrect SQL when you turn it off and your problem still won't be solved.