3 Replies - 1041 Views - Last Post: 23 April 2012 - 09:36 AM

#1 landopr786  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 17-April 12

Separate totals by date

Posted 17 April 2012 - 10:46 AM

Hi, I have been working several hours with this problem but cannot seem to find a solution. I have learned about several commands while doing this such as CUBE and ROLLUP but none seem to apply.
With a select clause I come up with the follwing data:
USER     DATE             HOURS          SOLD
user1	2012-04-17	3.850000	100
user1	2012-04-17	3.850000	100
user1	2012-04-18	3.850000	100
user1	2012-04-18	3.850000	50
user2	2012-03-30	3.000000	210
user2	2012-03-30	3.000000	100
user2	2012-03-31	3.000000	100



What I would like to have is the following:
USER     DATE             HOURS          SOLD
user1	2012-04-17	3.850000	200
user1	2012-04-18	3.850000	150
user2	2012-03-30	3.000000	310
user2	2012-03-31	3.000000	100



If I try to use the SUM(Sold) rather than SOLD I get the total repeated in each like. It is not separating it by date. I was hoping someone could point me in the right direction as to how this can be achieved.

Is This A Good Question/Topic? 0
  • +

Replies To: Separate totals by date

#2 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 262
  • View blog
  • Posts: 1,467
  • Joined: 07-April 08

Re: Separate totals by date

Posted 17 April 2012 - 11:02 AM

What Select statement are you using that gives you the first result you showed. Also post any relevant table structures. I suspect there might be an issue with a join your doing, but with out seeing your select statement I won't be able to say for sure.
Was This Post Helpful? 1
  • +
  • -

#3 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Separate totals by date

Posted 17 April 2012 - 11:19 AM

Try out this query.

SELECT [user], [date], [hours], SUM(sold) 
FROM Table_1
GROUP BY [date], [user], [hours]
ORDER BY [user]



And I'd like to suggest one little thing. Try not to use SQL keywords as column names. (That's why I've wrapped them with square brackets)
Was This Post Helpful? 2
  • +
  • -

#4 landopr786  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 17-April 12

Re: Separate totals by date

Posted 23 April 2012 - 09:36 AM

The solution was both, first there was something wrong with the joins in the query and secondly nK0des method work great. Thanks guys.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1