CODEWITHSUNDEEP
mysqlsqlsql-serverdatabase
user3071909
user3071909

Reputation: 15

SQL MIN not working correctly

I have a database where I am trying to count the total cancellations and changes. The problem is that their are duplicate rows in the database and I only want to use one row for each id. In the example below, I want my final count to be cancellations:5 , changes:1 . The sum part is working correctly, but the MIN(id) is selecting every row and calculating, instead of only one row per id. Thanks for any help., I have included my sql below

cancellations | changes | id  |orderNumber |
--------------|---------|-----|------------|
3             |1        | 5   |4           |
3             |1        | 5   |4           |
2             |0        | 7   |5           |


SELECT SUM(cancellations),SUM(changes)
FROM table_1
WHERE id = (SELECT MIN(id)) 
AND orderNumber > 3
AND (cancellations >0) AND id = (SELECT       
MIN(id))

Upvotes: 0

Views: 1182

Answers (4)

tshoemake
tshoemake

Reputation: 1351

Nevermind, didnt see mysql tag. Pretty sure it doesnt support ctes.

With cte 
as (
select cancellations, changes, id, orders, 
  ROW_NUMBER() OVER (PARTITION BY id
                            ORDER BY  id DESC ) RN
 from table1
)
select  
sum(cancellations) as cancellationtotal, 
sum(changes) as changetotal
from cte 
where rn<=1

Fiddle Demo

Upvotes: 0

t1t1an0
t1t1an0

Reputation: 281

i broke down the parts of your query into smaller ones and made a whole query out of the parts in this sql fiddle, Fiddle Here

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You have to use DISTINCT:

SELECT
    SUM(cancellations), SUM(changes)
FROM (
    SELECT DISTINCT * FROM table_1
) AS t

Upvotes: 0

Harsh
Harsh

Reputation: 1319

I am not sure I understood your requirement correctly, but are you looking for something like this?

SELECT id, SUM(distinct cancellations),SUM(distinct changes)
FROM table
group by id

Upvotes: 0

Related Questions