• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

group by and sum function in SQL

I'm trying to create a time management application where users can log the amount of time they spend on each project.

The fields are:
firstName, lastName, projectName, date, timeSpent

Users can add multiple records for each project per day and a project can take multiple days to complete so how can I
1. sum the hours spent on a project for that day
2. sort by last name
0
mattphung
Asked:
mattphung
  • 6
  • 2
  • 2
  • +1
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT firstName, lastName, projectName, date, SUM(timeSpent) tottime
from tableName
GROUP BY firstName, lastName, projectName, date
ORDER BY LastName
0
 
mattphungAuthor Commented:
I tried this and it doesn't work. I need to be able to sum the timespent on a project for that day.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
please paste the sample data and desired output
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mattphungAuthor Commented:

SELECT
	FirstName, 
	LastName,  
	CONVERT(VARCHAR(10), [Date], 101),
	SUM(TimeSpent),
	S.ProjectName, 
	S.UserId,		
	FROM [Project] AS S (NOLOCK)
	INNER JOIN [User] AS U (NOLOCK) ON S.UserId = U.Id
	WHERE S.LocationId = @LocationId 
	GROUP BY LastName, FirstName, ProjectName, [Date], UserId;

Open in new window

0
 
mattphungAuthor Commented:
here is the results:

Charisse      Chocolate      12/29/2009      60.00      1      19
Charisse      Chocolate      12/30/2009      60.00      1      19
Charisse      Chocolate      12/30/2009      60.00      1      19
Jasmine      Nelly      12/30/2009      75.00      1      18
Jasmine      Nelly      12/30/2009      60.00      1      18
Robert      Kang      12/30/2009      60.00      1      20
0
 
mattphungAuthor Commented:
I need to combine
Charisse      Chocolate      12/30/2009      60.00      1      19
Charisse      Chocolate      12/30/2009      60.00      1      19

to give
Charisse      Chocolate      12/30/2009      120.00      1      19
0
 
SharathData EngineerCommented:
check this
SELECT
        FirstName, 
        LastName,  
        CONVERT(VARCHAR(10), [Date], 101),
        SUM(TimeSpent),
        S.ProjectName, 
        S.UserId,               
        FROM [Project] AS S (NOLOCK)
        INNER JOIN [User] AS U (NOLOCK) ON S.UserId = U.Id
        WHERE S.LocationId = @LocationId 
        GROUP BY LastName, FirstName, ProjectName, CONVERT(VARCHAR(10), [Date], 101), UserId;

Open in new window

0
 
ChizlCommented:
Aneeshattingal, will work if you make one change.   Based on datetime in SQL, you will have Time as well as Date, cause each record to show up as if you just did a select * from tablename.  you must convert it, then format it without the time during the group.

Try the attached code.
declare @tmp as table (firstName varchar(50), lastName varchar(50), projectName varchar(50), date datetime, timeSpent int)

insert @tmp values ('a', 'aa', 'aaa', '01/01/2008 01:00:00', 1)
insert @tmp values ('a', 'aa', 'aaa', '01/01/2008 05:00:00', 5)
insert @tmp values ('b', 'bb', 'aaa', '01/01/2008 01:01:00', 2)
insert @tmp values ('c', 'cc', 'bbb', '01/01/2008 01:02:00', 3)
insert @tmp values ('a', 'aa', 'bbb', '01/02/2008 01:00:00', 2)
insert @tmp values ('b', 'bb', 'ccc', '01/02/2008 01:01:00', 3)
insert @tmp values ('c', 'cc', 'ccc', '01/02/2008 01:02:00', 3)

select lastName+', '+firstName
, projectName
, convert(varchar(10), date, 101) 'date'
, sum(timeSpent) tottime
from @tmp 
group by firstName, lastName, projectName, convert(varchar(10), date, 101)
order by lastName

output:
aa, a	aaa	01/01/2008	6
aa, a	bbb	01/02/2008	2
bb, b	aaa	01/01/2008	2
bb, b	ccc	01/02/2008	3
cc, c	bbb	01/01/2008	3
cc, c	ccc	01/02/2008	3

Open in new window

0
 
mattphungAuthor Commented:
yeah!!! Aneesh's code works perfectly!
Can you explain why my didn't work and how you fixed it.

chizl: thanks for your help. I thought of that too
0
 
SharathData EngineerCommented:
mattphung - did you check my suggestion also? I guess you have different times for the same date as provided in Chizl's  sample set. Thats the reason, you are ended up with multiple records for the same combination.

aneeshattingal's code will group by entire date ( date+time). If you want to group by only date and at the same time group the records, you need to try as I mentioned.
0
 
mattphungAuthor Commented:
sorry.... Sharath you code works. Thank you very much for the help.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now