CODEWITHSUNDEEP
sqlsql-server
Sir Meysam Ferguson
Sir Meysam Ferguson

Reputation: 193

Suming up Rows Based On Repetition Of Another Column

I Don't Know Why My Previous Topic Was Marked!!!???

This Is What I've Done So Far. I Can't Think Of Anything Else.

enter image description here

So Many Things I've Done To Reach To This Point Which Is Illustrated In My Problem Section. Which For Simplification I Made Name A,B,C,... .I'm Not Giving You My Homework.

My Problem:

enter image description here

Upvotes: 0

Views: 51

Answers (2)

T I
T I

Reputation: 9933

SELECT [Name], [Value]
FROM (
    SELECT *, RANK() OVER(ORDER BY [Count] DESC, Value DESC) [rn]
    FROM (
        SELECT [Name], SUM(Value) [Value], COUNT(1) [Count]
        FROM MyTable
        GROUP BY Name
    ) t
) t
WHERE rn = 1

SQL Fiddle

or more simply

SELECT TOP 1 WITH TIES [Name], [Value]
FROM (
    SELECT [Name], SUM(Value) [Value], COUNT(1) [Count]
    FROM MyTable
    GROUP BY Name
) t
ORDER BY [Count] DESC, Value DESC

SQL Fiddle

Upvotes: 0

wumpz
wumpz

Reputation: 9131

Here is a SQL for SqlServer 2008 to solve your problem.

with data as (select name, count(*) as occurrence, sum(value) as sumvalue from mytab group by name)
select * from data where
  occurrence=(select max(occurrence) from data)
  and sumvalue=(select max(sumvalue) from data data2 where data2.occurrence=data.occurrence)

In data the needed values are collected (sum and count). Now we select the rows with max occurrence and filtering out the rows with the max value.

http://sqlfiddle.com/#!3/56b00/4

Upvotes: 1

Related Questions