I am back and need some help with Group By query. I am using SQL Server 2000. I have table in a database from which I have to SELECT some records. The first field is
callingPartyNumber. I am trying to SELECT a few fields including
callingPartyNumber, and SUM(duration). Is it possible to group the results according to the
callingPartyNumber field only (knowing the fact that I have other fields also in the SELECT part of the query)?
The type of output I want is that all the records are grouped by the callingPartyNumber field and the sum of duration field for each groups is also returned.
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
I cannot normalize the tables (implies that I have to work on one and only one table).
No, you can't, and it doesn't make any sense. If all the other fields are interesting as they are, then you need to include them in the GROUP BY. If it's just to get the total duration on every row, well then:
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.totalduration
FROM Calls c
INNER JOIN (
SELECT callingPartyNumber,
SUM(CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108)) totalduration
FROM Calls
) d ON c.callingPartyNumber=d.callingPartyNumber
-- "Mark As Answer" if my reply helped you --
Marked as answer by bullpit on Jul 27, 2007 04:13 PM
Why doesn't it make sense? What if I just want to display the fields in SELECT and group only by callingPartyNumber. I am hoping that the query that you gave does that but I am getting an error.
No column was specified for column 2 of 'd'.
And this is the query:
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.totalduration
FROM Calls c
INNER JOIN (
SELECT callingPartyNumber,SUM(duration)
FROM Calls
) d ON c.callingPartyNumber=d.callingPartyNumber
What do you expect your recordset to look like? Because your summing a column and displaying data, you have to group by the columns that you are interested it. If your not intersted in them, don't include them in your select and you'll get the sum value.
But because you're wanting to return those fields, you'll need to tell sql to group by those fields so sql can sum then separately.
He he he...I did see that earlier but deleted it when copied the query (by mistake). After I posted, I realized it needed a name so supplied it. Now getting the same ol GROUP BY error, back to square one...
Column 'CallDetailRecord.callingPartyNumber' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
What do you expect your recordset to look like? Because your summing a column and displaying data, you have to group by the columns that you are interested it. If your not intersted in them, don't include them in your select and you'll get the sum value.
But because you're wanting to return those fields, you'll need to tell sql to group by those fields so sql can sum then separately.
If I group by all the fields in the SELECT clause, it will progressively GROUP BY all the fields. That means that if there is a difference in any of the other fields, the clause will treat is as a different record. I don't want that. I want all the same
callingPartyNumbers to be grouped together no matter what data is there in other fields and unltimately sum up the duration for each caller.
bullpit
All-Star
21838 Points
4822 Posts
SQL Group By query
Jul 26, 2007 06:42 PM|LINK
I am back and need some help with Group By query. I am using SQL Server 2000. I have table in a database from which I have to SELECT some records. The first field is callingPartyNumber. I am trying to SELECT a few fields including callingPartyNumber, and SUM(duration). Is it possible to group the results according to the callingPartyNumber field only (knowing the fact that I have other fields also in the SELECT part of the query)?
The type of output I want is that all the records are grouped by the callingPartyNumber field and the sum of duration field for each groups is also returned.
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 I cannot normalize the tables (implies that I have to work on one and only one table).Max
Let Me Google That For You!
gunteman
All-Star
22406 Points
3305 Posts
Re: SQL Group By query
Jul 26, 2007 07:28 PM|LINK
No, you can't, and it doesn't make any sense. If all the other fields are interesting as they are, then you need to include them in the GROUP BY. If it's just to get the total duration on every row, well then:
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.totalduration FROM Calls c INNER JOIN ( SELECT callingPartyNumber, SUM(CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108)) totalduration FROM Calls ) d ON c.callingPartyNumber=d.callingPartyNumberbullpit
All-Star
21838 Points
4822 Posts
Re: SQL Group By query
Jul 26, 2007 07:57 PM|LINK
No column was specified for column 2 of 'd'.
And this is the query:
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.totalduration FROM Calls c INNER JOIN ( SELECT callingPartyNumber,SUM(duration) FROM Calls ) d ON c.callingPartyNumber=d.callingPartyNumberMax
Let Me Google That For You!
gunteman
All-Star
22406 Points
3305 Posts
Re: SQL Group By query
Jul 26, 2007 08:06 PM|LINK
Now, that isn't what I wrote, is it? [:)] (The duration part in the derived table must look as I wrote it)
If the result is not what you wanted, please show the desired output.
cdkimmel
Member
272 Points
71 Posts
Re: SQL Group By query
Jul 26, 2007 08:08 PM|LINK
What do you expect your recordset to look like? Because your summing a column and displaying data, you have to group by the columns that you are interested it. If your not intersted in them, don't include them in your select and you'll get the sum value. But because you're wanting to return those fields, you'll need to tell sql to group by those fields so sql can sum then separately.
bullpit
All-Star
21838 Points
4822 Posts
Re: SQL Group By query
Jul 26, 2007 08:11 PM|LINK
He he he...I did see that earlier but deleted it when copied the query (by mistake). After I posted, I realized it needed a name so supplied it. Now getting the same ol GROUP BY error, back to square one...
Column 'CallDetailRecord.callingPartyNumber' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Max
Let Me Google That For You!
cdkimmel
Member
272 Points
71 Posts
Re: SQL Group By query
Jul 26, 2007 08:15 PM|LINK
then group by it..
group by callingPartyNumber'
bullpit
All-Star
21838 Points
4822 Posts
Re: SQL Group By query
Jul 26, 2007 08:16 PM|LINK
If I group by all the fields in the SELECT clause, it will progressively GROUP BY all the fields. That means that if there is a difference in any of the other fields, the clause will treat is as a different record. I don't want that. I want all the same callingPartyNumbers to be grouped together no matter what data is there in other fields and unltimately sum up the duration for each caller.
Max
Let Me Google That For You!
cdkimmel
Member
272 Points
71 Posts
Re: SQL Group By query
Jul 26, 2007 08:17 PM|LINK
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)
bullpit
All-Star
21838 Points
4822 Posts
Re: SQL Group By query
Jul 26, 2007 08:18 PM|LINK
Where are you suggesting to put this.
Max
Let Me Google That For You!