Select SUM MAX Display Person Name

Question

Post reply

Select SUM MAX Display Person Name

rahulwalawalkar

SSC-Addicted

Points: 457
More actions
July 3, 2010 at 3:55 am

#226415

Hi
Please can anyone let me how can i do the following
state amt per
NSW130person1
QLD100person1
NSW120person2
QLD200person2
NSW110person3
QLD300person3
I want to show sum and max values but list person name instead of max value
per state
SELECT T1.Sate,SUM(T1.Amt),max(t1.amt)
FROM State_Sales T1
GROUP BY T1.Sate
output
NSW360130
QLD600300
i want to display person1 instead of 130 and person3 instead of 300
can anyone let me know how can i do it
Cheers
Rahul

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 1

Try this -

CREATE TABLE #Temp ( state varchar(10), amt money, per varchar(10) )

INSERT INTO #Temp

SELECT 'NSW','130','person1'

UNION ALL

SELECT 'QLD','100','person1'

UNION ALL

SELECT 'NSW','120','person2'

UNION ALL

SELECT 'QLD','200','person2'

UNION ALL

SELECT 'NSW','110','person3'

UNION ALL

SELECT 'QLD','300','person3'

SELECT a.State, a.TotAmt, b.Per FROM

(

SELECTT.state, SUM(t.amt) TotAmt

FROM#temp T

GROUP BYt.State

) a

INNER JOIN

(

SELECTState, Per FROM

(

SELECT State, amt, per, ROW_NUMBER() OVER ( Partition BY State ORDER BY amt DESC ) RowNo FROM #Temp

) dWHERE RowNo = 1

) b ON a.State = b.State

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

rahulwalawalkar SSC-Addicted Points: 457 More actions · Answer 2

Hi

Thanks it works, if possible can you explain the query a bit more

Cheers

Rahul

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Here are another couple of CTE-based variations. They may be easier to read and understand. Basically each does a summary query to total the amounts for each state, then does another query to get the person who had the highest amount. In production, data volumes and indexing will control which will run most efficiently.

;with cte as(

select t.state,SUM(t.amt) as TotAmt,max(t.amt) as MaxAmt

from #Temp t

group by t.state

)

select c.state,c.TotAmt,ca.per

from cte c

cross apply (select per from #temp t where t.state = c.state and t.amt = c.MaxAmt) ca

;with cte as(

select t.state,SUM(t.amt) as TotAmt,max(t.amt) as MaxAmt

from #Temp t

group by t.state

)

select c.state,c.TotAmt,ca.per

from cte c

cross apply (select top 1 per from #temp t where t.state = c.state order by t.amt desc) ca

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 4

Hi Raul,

Here is an other solution

with cte as (

select

State,

amt,

per,

rn = ROW_NUMBER() over (partition by state order by amt desc)

from summax

)

select

state,

SUM(amt) total,

per = (select cte2.per from cte cte2 where cte2.state = cte.state and cte2.rn = 1)

from cte

group by state

I hope that helps,

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

And Another, all aggregate function support the OVER clause.

with cte

as

(

select state,

Per,

SUM(amt) over(partition by state) as TotAmt,

Row_number() over(partition by State order by Amt desc) as RowN from #Temp

)

select * from cte where RowN = 1

Clear Sky SQL
My Blog[/url]

Ashwani Malik Ten Centuries Points: 1140 More actions · Answer 6

CREATE TABLE T2 ( state varchar(10), amt money, per varchar(10) )

INSERT INTO t2

SELECT 'NSW','130','person1'

UNION ALL

SELECT 'QLD','100','person1'

UNION ALL

SELECT 'NSW','120','person2'

UNION ALL

SELECT 'QLD','200','person2'

UNION ALL

SELECT 'NSW','110','person3'

UNION ALL

SELECT 'QLD','300','person3'

WITH A ( state, Totalvalue, Maxvalue) as

(SELECT State,SUM(Amt),max(amt)

FROM T2

GROUP BY State)

Select A.state,A.totalvalue, t2.Per from A inner join T2 on a.state=T2.state and a.Maxvalue=T2.amt

Answer 7

Hi,

The Dixie Flatline using Comman Table Experssions concept good and also this query return correct values. Another Person also gave the result (using inner join concept) with using row number concept.

But this result fail for some times.

For an example :

State Amount Person

***** ******* ******

TN 100 Sethu

TN 200 Bala

TN 200 Arun

KN 300 Kumar

KN 130 Sathish

In this case State TN - Same Max value having more than one

person that time query return only one person. But our result like bala and arun

Thanks & Regards,

G.Sethuraj.

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 8

Hi Sethuraj,

You can try using T-SQL DENSE_RANK() function instead of using ROW_NUMBER function.

Dense Rank will take care of rows having same value

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Answer 9

Hi Eralper,

Thanks for your replay. Now i got correct answer.

Today i learn some thing from u.