SQL Group By query : The Official Microsoft ASP.NET Forums

Reply

Re: SQL Group By query

Jul 27, 2007 04:01 PM|LINK

I think I finally got it working but will post back if theres any problem.

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 Calls c




INNER JOIN (
	SELECT callingPartyNumber, CONVERT(char(8), DATEADD(second, SUM(duration), '0:00:00'), 108) AS totDuration
	FROM Calls
        WHERE
(datetimeConnect - 14400) >= convert(bigint,datediff(ss,  '01-01-1970 00:00:00', '07-27-2007 12:00:00 AM')) AND
(dateTimeDisconnect  - 14400) <= convert(bigint,datediff(ss,  '01-01-1970 00:00:00', '07-27-2007 11:59:59 PM'))
        GROUP BY callingPartyNumber


	) d ON c.callingPartyNumber = d.callingPartyNumber AND
(datetimeConnect - 14400) >= convert(bigint,datediff(ss,  '01-01-1970 00:00:00', '07-27-2007 12:00:00 AM')) AND
(dateTimeDisconnect  - 14400) <= convert(bigint,datediff(ss,  '01-01-1970 00:00:00', '07-27-2007 11:59:59 PM')) AND

Thanks,
Max
Let Me Google That For You!

Reply

Re: SQL Group By query

Jul 27, 2007 09:58 PM|LINK

bullpit

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?

No, it's pretty easy actually, but you have to be able to describe what you want accurately. Like:

I want all the records in the calls table for a particular day, and on each record, I want to add an additional column that shows the total duration of all the calls the calling party made that day.

See in order to solve a problem you must either be able to describe what you want clearly, or to be able to show what you have and what you want it to look like. You haven't given us an example set of data, and your description of what you want is extremely inaccurate and vague. For example, you said you wanted it "grouped by the first column", but that is inaccurate. Grouping by means something very specific, but it's not what you wanted at all. At the very least the records aren't grouped by the callingparty, but the TotalDuration column may be calculated using a group by, depending on how you implement it. Next you say " with the additional information in other columns, and the total duration". See, you asked for some additional information in other columns AND the total duration. You meant you wanted "an additional column called total duration that...". Or perhaps you meant "including all the relevant details (originalCalledPartyNumber,finalCalledPartyNumber,dateTimeConnect,dateTimeDisconnect) about the call, and an additional column called total duration that..." You also failed to mention that you wanted the results restricted to a particular day and that the Total Duration column should be restricted for a day.

95% of solving this problem is trying to get you to accurately describe what you want.

Reply

bullpit

All-Star

21838 Points

4822 Posts

Re: SQL Group By query

Jul 30, 2007 12:46 PM|LINK

Heres some samle data with what I want and what I am getting. Please let me know if it still is not clear.

INPUT TABLE

callingPartyNumber	originalCalledPartyNumber	finalcalledPartyNumber	dateTimeConnected	dateTimeDisconnected	Duration (HH:MM:SS)
1234	5678	5678	#######	######	00:02:00
1234	5678	1000	#######	######	00:04:00
9078	5678	1000	#######	######	00:03:00
1234	5678	1000	#######	######	00:02:00
9078	5678	1000	#######	######	00:05:00
9078	5678	1000	#######	######	00:10:00
1234	5678	1000	#######	######	00:02:00

IDEAL OUTPUT

callingPartyNumber	originalCalledPartyNumber	finalcalledPartyNumber	dateTimeConnected	dateTimeDisconnected	Duration (HH:MM:SS)
1234	5678	5678	#######	######	00:02:00
1234	5678	1000	#######	######	00:04:00
1234	5678	1000	#######	######	00:02:00
1234	5678	1000	#######	######	00:02:00
				Total	00:10:00
9078	5678	1000	#######	######	00:03:00
9078	5678	1000	#######	######	00:05:00
9078	5678	1000	#######	######	00:10:00
				Total	00:18:00

OUTPUT I AM GETTING:

callingPartyNumber	originalCalledPartyNumber	finalcalledPartyNumber	dateTimeConnected	dateTimeDisconnected	Duration (HH:MM:SS)	Total Duration
1234	5678	5678	#######	######	00:02:00	00:10:00
1234	5678	1000	#######	######	00:04:00	00:10:00
1234	5678	1000	#######	######	00:02:00	00:10:00
1234	5678	1000	#######	######	00:02:00	00:10:00
9078	5678	1000	#######	######	00:03:00	00:18:00
9078	5678	1000	#######	######	00:05:00	00:18:00
9078	5678	1000	#######	######	00:10:00	00:18:00

The IDEAL OUTPUT need not look like this in SQL Server table. This is the display I want to show to the user (using GridView). Please give your suggestions.

Motley- About that added clause of limiting results to a day, its actually limiting results accoriding to any FromDate and ToDate. I did not give that part of the query in my original problem because there are some other parameters (filters) also in WHERE clause and I did not want to make the query very complex to work on (and I knew I could fit the WHERE clause in the query if I had the basic thing working, and I did).

Thanks,
Max
Let Me Google That For You!

Edited by bullpit on Jul 30, 2007 12:53 PM

Reply

ndinakar

All-Star

49092 Points

6868 Posts

Moderator

MVP

Re: SQL Group By query

Jul 30, 2007 03:58 PM|LINK

You can easily do this kind of sub totals at the front end. I think gridview has that option, perhaps not built in but you can write code to do it. HEre's something I googled for you: http://www.agrinei.com/gridviewhelper/gridviewhelper_en.htm

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

Reply

bullpit

All-Star

21838 Points

4822 Posts

Re: SQL Group By query

Jul 30, 2007 04:04 PM|LINK

Thanks buddy. It seems like it does exactly what I want. I will work on it and let you know what happened. May be all this time I was looking at the wrong end[;)]

Thanks,
Max
Let Me Google That For You!

Reply

gunteman

All-Star

22406 Points

3305 Posts

Re: SQL Group By query

Jul 30, 2007 04:25 PM|LINK

Well, this should indeed be a presentation layer issue.

http://www.asp.net/learn/data-access/tutorial-27-vb.aspx outlines possible techniques for this (manipulate a datatable before it's bound to the GridView or manipulate the GridView before it's rendered)

It is, however, possible to do it in SQL

SELECT type,callingPartyNumber,originalCalledPartyNumber,finalcalledPartyNumber,dateTimeConnected,dateTimeDisconnected,CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration
FROM
(
	SELECT 0 as type,callingPartyNumber,originalCalledPartyNumber,finalcalledPartyNumber,dateTimeConnected,dateTimeDisconnected,duration FROM calls
	

	UNION
 

	SELECT 1,callingPartyNumber,null,null,null,null,SUM(duration) FROM calls GROUP BY callingPartyNumber
) dt
ORDER BY callingPartyNumber,type

Another possible solution would be to use ROLLUP in SQL

-- "Mark As Answer" if my reply helped you --

SQL Group By query RSS

25 replies

bullpit

Re: SQL Group By query

Motley

Re: SQL Group By query

bullpit

bullpit

Re: SQL Group By query

ndinakar

Re: SQL Group By query

bullpit

Re: SQL Group By query

gunteman

Re: SQL Group By query

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft