CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mahajan344
Mahajan344

Reputation: 2550

issue with group by when order by is used

i am having difficulty when i try to group by but with using order by statement.

Here is my query

create table AllData(NoOfPerson int,NoOfMinutes int,StartTime Datetime);

INSERT INTO AllData VALUES(1,2,GETDATE()),
(0,3,GETDATE()+1),
(3,4,GETDATE()+2),
(2,5,GETDATE()+3),
(0,6,GETDATE()+4),
(3,7,GETDATE()+5),
(2,8,GETDATE()+6);

and output from the query 

select NoOfperson,SUM(NoOfMinutes)NoOfMinutes,MIN(StartTime)StartTime from AllData
group by NoOfperson,StartTime
order by StartTime

NoOfperson  NoOfMinutes StartTime
1               2   2014-02-19 15:44:52.617
0               3   2014-02-20 15:44:52.617
3               4   2014-02-21 15:44:52.617
2               5   2014-02-22 15:44:52.617
0               6   2014-02-23 15:44:52.617
3               7   2014-02-24 15:44:52.617
2               8   2014-02-25 15:44:52.617

But i want output should be like

first 

1  -- 2
0  -- 9
3  -- 11
2  -- 13

How can i get this output ?

Upvotes: 0

Views: 44

Answers (4)

KumarHarsh
KumarHarsh

Reputation: 5094

what about this,

select * , 
sum(NoOfMinutes)over(partition by NoOfPerson,NoOfPerson order by  NoOfPerson) 
from @AllData

Upvotes: 0

ziollek
ziollek

Reputation: 1993

try wrap query:

SELECT * 
FROM   (SELECT noofperson, 
               Sum(noofminutes) NoOfMinutes, 
               Min(starttime)   StartTime 
        FROM   alldata 
        GROUP  BY noofperson) t 
ORDER  BY noofminutes ASC;

or simply:

SELECT noofperson, 
       Sum(noofminutes) NoOfMinutes, 
       Min(starttime)   StartTime 
FROM   alldata 
GROUP  BY noofperson 
ORDER  BY noofminutes ASC;

Upvotes: 3

Antonio
Antonio

Reputation: 535

SELECT NoOfperson, SUM(NoOfMinutes) AS SumNoOfMinutes , MIN(StartTime)StartTime
from AllData
group by NoOfperson
order by SumNoOfMinutes asc ;

Upvotes: 0

Abhishek Chaudhary
Abhishek Chaudhary

Reputation: 807

Try this:

SELECT NoOfperson, NoOfMinutes FROM
(
select NoOfperson,SUM(NoOfMinutes)NoOfMinutes,MIN(StartTime)StartTime from AllData
group by NoOfperson
) AS Data
GROUP BY NoOfperson, NoOfMinutes, StartTime
order by StartTime

Upvotes: 0

Related Questions