SQL Group By query RSS

• • Reply
  

  bullpit

  All-Star

  21838 Points

  4822 Posts

  Re: SQL Group By query

  Jul 26, 2007 08:20 PM|LINK

  cdkimmel

  SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber,
         dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00') AS dateTimeConnect,
         dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00') AS dateTimeDisconnect,
         CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration,
         SUM(duration)
  
  FROM Calls
   
  GROUP BY callingPartyNumber,dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00'), dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00'), CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108)

  Please refer to post 8

  Thanks,
  Max
  Let Me Google That For You!
  • Edited by bullpit on Jul 26, 2007 08:21 PM
• • Reply
  

  cdkimmel

  Member

  272 Points

  71 Posts

  Re: SQL Group By query

  Jul 26, 2007 08:25 PM|LINK

  If you have an aggregate function (sum) in your select you must group by the other selected columns in the query.

  You can:

  select sum(duration) from Calls
  select callingPartyNumber, sum(duration) from calls group by callingPartyNumber
  select callingPartyNumber, dateTimeConnect, sum(duration) from calls group by callingPartyNumber, dateTimeConnect

  You can't:

  select callingPartyNumber, dateTimeConnect, sum(duration) from calls group by callingPartyNumber

  bullpit

  cdkimmel

  SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber,
         dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00') AS dateTimeConnect,
         dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00') AS dateTimeDisconnect,
         CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration,
         SUM(duration)
  
  FROM Calls
   
  GROUP BY callingPartyNumber,dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00'), dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00'), CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108)

  Please refer to post 8

• • Reply
  

  bullpit

  All-Star

  21838 Points

  4822 Posts

  Re: SQL Group By query

  Jul 26, 2007 08:27 PM|LINK

  Thanks but I know that. I just want a workaround or a query.

  Thanks,
  Max
  Let Me Google That For You!
• • Reply
  

  cdkimmel

  Member

  272 Points

  71 Posts

  Re: SQL Group By query

  Jul 26, 2007 08:39 PM|LINK

  hate to say it but all I can think of is a cursor in a proc...

  But check this guy's link out..he discusses using cross join, don't know if that's available in 2000 though.

  http://www.sqlteam.com/article/calculating-running-totals

• • Reply
  

  Motley

  Star

  13789 Points

  2449 Posts

  MVP

  Re: SQL Group By query

  Jul 26, 2007 08:41 PM|LINK

  Here you go.. Results by callingPartyNumber, with the duration summed, with some values that relate to the calling party. 

  SELECT callingPartyNumber, MAX(originalCalledPartyNumber), MAX(finalCalledPartyNumber),
         MAX(dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00')) AS dateTimeConnect,
         MAX(dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00')) AS dateTimeDisconnect,
         MAX(CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108)) AS duration,
         SUM(duration)
  
  FROM Calls
   
  GROUP BY callingPartyNumber
  

  The above query meets all the requirements you've asked for.  Personally, I think the results are complete crap, but run it, and tell me what you don't like about it so that we have some way to move forward.

  • Edited by Motley on Jul 26, 2007 08:47 PM
• • Reply
  

  cdkimmel

  Member

  272 Points

  71 Posts

  Re: SQL Group By query

  Jul 26, 2007 08:48 PM|LINK

  that's interesting... 

  Motley

  Here you go.. Results by callingPartyNumber, with the duration summed, with some values that relate to the calling party. 

  SELECT callingPartyNumber, MAX(originalCalledPartyNumber), MAX(finalCalledPartyNumber),
         MAX(dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00')) AS dateTimeConnect,
         MAX(dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00')) AS dateTimeDisconnect,
         MAX(CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108)) AS duration,
         SUM(duration)
  
  FROM Calls
   
  GROUP BY callingPartyNumber
  

  The above query meets all the requirements you've asked for.  Personally, I think the results are complete crap, but run it, and tell me what you don't like about it so that we have some way to move forward.

• • Reply
  

  bullpit

  All-Star

  21838 Points

  4822 Posts

  Re: SQL Group By query

  Jul 26, 2007 08:48 PM|LINK

  Thank a lot everybody. Will try you guys' suggestions tomorrow and will get back.

  Thanks again.

  Thanks,
  Max
  Let Me Google That For You!
• • Reply
  

  bullpit

  All-Star

  21838 Points

  4822 Posts

  Re: SQL Group By query

  Jul 27, 2007 02:43 PM|LINK

  Motley

  SELECT callingPartyNumber, MAX(originalCalledPartyNumber), MAX(finalCalledPartyNumber),
         MAX(dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00')) AS dateTimeConnect,
         MAX(dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00')) AS dateTimeDisconnect,
         MAX(CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108)) AS duration,
         SUM(duration)
  
  FROM Calls
   
  GROUP BY callingPartyNumber
  

  Motley-

  Can you please explain what the above query does. I sure am getting all crap results. I don't necessarily have to use GROUP BY. The previous query using INNER JOIN seemed to have the logic I wanted but can't get it to work.

  Again, what I want is to display all the records grouped by the first column, i.e. callingPartyNumber, with the additional information in other columns, and the total duration (SUM(duration)) for each callingPartyNumber. I can get it work if I only have callingPartyNumber and SUM in SELECT. Is it so hard to display additional information?

  Thanks,
  Max
  Let Me Google That For You!
  • Edited by bullpit on Jul 27, 2007 02:43 PM
  • Edited by bullpit on Jul 27, 2007 02:47 PM
• • Reply
  

  bullpit

  All-Star

  21838 Points

  4822 Posts

  Re: SQL Group By query

  Jul 27, 2007 03:10 PM|LINK

  Now this seems to work but I cant put a WHERE clause in between. Can anyone help with that? 

  SELECT c.callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber,
         dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00') AS dateTimeConnect,
         dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00') AS dateTimeDisconnect,
         CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration, d.totDuration
         
  
  FROM CallDetailRecord c
  
  
  INNER JOIN (
  	SELECT callingPartyNumber, SUM(duration) totDuration
  	FROM CallDetailRecord
  
  
          GROUP BY callingPartyNumber
  	) d ON c.callingPartyNumber = d.callingPartyNumber

   

  Thanks,
  Max
  Let Me Google That For You!
• • Reply
  

  ndinakar

  All-Star

  49092 Points

  6868 Posts

  Moderator

  MVP

  Re: SQL Group By query

  Jul 27, 2007 03:58 PM|LINK

  Can you provide some sample data and expected result so there is less chance of confusion?

  ***********************
  Dinakar Nethi
  Life is short. Enjoy it.
  ***********************