SQL Query Syntax Question
A user on IDS 11.50FC6W2 (Linux) got error -201 (syntax error) in a single aggregate SELECT over a call-transaction table. COUNT(UNIQUE callrec) worked as a standalone column, and COUNT(*)/COUNT(ALL col)/COUNT(col) worked inside arithmetic, but SUM(...)/COUNT(UNIQUE|DISTINCT col) failed. A missing parenthesis was suggested first, but didn't fix it; he then found only one COUNT(UNIQUE ...) could appear per query. Dave Griffen suggested moving the total_calls COUNT(UNIQUE callrec) into a correlated-criteria sub-SELECT so the remaining one works; no confirmation of success or explanation of the underlying cause is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a kind of strange syntax error happening on a select statement, and I need a little help. Engine 11.50FC6W2 - Red Hat Linux I have a table that tracks transactions of a telephone call. The table has 4 columns: transid serial, userid char(30), callrec char(32), transstart datetime year to second, transend datetime year to second Each phone call will have a unique callrec, and a phone call can have multiple transactions. So I'm trying to get total transactions, total duration, average transaction duration, min transaction duration, max transaction duration, total calls and average call duration for a given userid for a given date range. Here's the query that I came up with SELECT COUNT(*) as total_transactions, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/3600 as total_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/count(*) as avg_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as min_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as max_trans_duration, COUNT(UNIQUE callrec) as total_calls, (SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) / COUNT(UNIQUE callrec)/60 as avg_call_duration FROM calltrans WHERE transstart BETWEEN ? AND ? AND userid = ? The statement works perfectly except for the last column for avg_call_duration. I get a syntax error on the COUNT(UNIQUE callrec) part of the statement. I can use COUNT(*) without any error, and I can use COUNT(ALL callrec) or COUNT(callrec) without error. I just can't use UNIQUE or DISTINCT with COUNT in the calculation. The COUNT(UNIQUE callrec) for total_calls works perfectly. I know I can do this in a stored procedure, but I really wanted to get this done with one statement. I read through the 11.50 SQL docs on aggregate functions, and unless I'm reading it wrong, it should work. Am I wrong or could this be a bug? <html> <body> <font size="1"> Teleformix Confidentiality Statement and Notice: This message and any associated files are covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521. This message and any attached content are for INTENDED RECEIPT ONLY. It may contain privileged or confidential information. If you have received this communication in error, please delete all information both electronic and hard copy. The information contained herein may contain information that is privileged or confidential, and may be subject to additional restrictions. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message is strictly prohibited. If you are the intended recipient or the employee or agent responsible for delivering this communication to the intended recipient, you are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication is strictly proh ibited. All attached documents and files are classified CONFIDENTIAL and for INTENDED RECIPIENT ONLY. If you need additional information or need to report any incidents please call 630.285.6500. Your attention in this matter is greatly appreciated. </font> </body> </html>
Try this coding for the last column: (SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) / COUNT(UNIQUE callrec))/60 as avg_call_duration I count a missing closing paranthesis, fixed above. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 9, 2010 at 12:34 PM, Jamie Gedye <jgedyedba@teleformix.com>wrote: > I have a kind of strange syntax error happening on a select statement, and > I > need a little help. > > Engine 11.50FC6W2 - Red Hat Linux > > I have a table that tracks transactions of a telephone call. The table has > 4 columns: > > transid serial, > userid char(30), > callrec char(32), > transstart datetime year to second, > transend datetime year to second > > Each phone call will have a unique callrec, and a phone call can have > multiple transactions. > > So I'm trying to get total transactions, total duration, average > transaction > duration, min transaction duration, max transaction duration, total calls > and average call duration for a given userid for a given date range. > > Here's the query that I came up with > > SELECT > COUNT(*) as total_transactions, > SUM(((transend - transstart)::interval second(9) to > second)::char(10)::int8)/3600 as total_trans_duration, SUM(((transend - > transstart)::interval second(9) to > second)::char(10)::int8)/count(*) as avg_trans_duration, SUM(((transend - > transstart)::interval second(9) to > second)::char(10)::int8) as min_trans_duration, SUM(((transend - > transstart)::interval second(9) to > second)::char(10)::int8) as max_trans_duration, COUNT(UNIQUE callrec) as > total_calls, (SUM(((transend - transstart)::interval second(9) to > second)::char(10)::int8) / COUNT(UNIQUE callrec)/60 as avg_call_duration > FROM calltrans WHERE transstart BETWEEN ? AND ? > AND userid = ? > > The statement works perfectly except for the last column for > avg_call_duration. > > I get a syntax error on the COUNT(UNIQUE callrec) part of the statement. > > I can use COUNT(*) without any error, and I can use COUNT(ALL callrec) or > COUNT(callrec) without error. > > I just can't use UNIQUE or DISTINCT with COUNT in the calculation. The > COUNT(UNIQUE callrec) for total_calls works perfectly. > > I know I can do this in a stored procedure, but I really wanted to get this > done with one statement. > > I read through the 11.50 SQL docs on aggregate functions, and unless I'm > reading it wrong, it should work. > > Am I wrong or could this be a bug? > > <html> > <body> > <font size="1"> > Teleformix Confidentiality Statement and Notice: This message and any > associated files are covered by the Electronic Communications Privacy Act, > 18 > U.S.C. 2510-2521. This message and any attached content are for INTENDED > RECEIPT ONLY. It may contain privileged or confidential information. If you > have received this communication in error, please delete all information > both > electronic and hard copy. The information contained herein may contain > information that is privileged or confidential, and may be subject to > additional restrictions. If you are not the intended recipient you are > hereby > notified that any dissemination, copying or distribution of this message, > or > files associated with this message is strictly prohibited. If you are the > intended recipient or the employee or agent responsible for delivering this > communication to the intended recipient, you are hereby notified that any > unauthorized use, dissemination, distribution or copying of this > communication > is strictly proh > ibited. All attached documents and files are classified CONFIDENTIAL and > for > INTENDED RECIPIENT ONLY. If you need additional information or need to > report > any incidents please call 630.285.6500. Your attention in this matter is > greatly appreciated. > </font> > </body> > </html> > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016368e28d0f2f9e10483d0990b
If you are going to run this regularly have you thought about keeping the second delta in the table, it would save a lot of conversion and casting and will be quicker ..... and easier to read :-) Cheers Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jamie Gedye Sent: Friday, April 09, 2010 11:35 AM To: ids@iiug.org Subject: SQL Query Syntax Question [19589] I have a kind of strange syntax error happening on a select statement, and I need a little help. Engine 11.50FC6W2 - Red Hat Linux I have a table that tracks transactions of a telephone call. The table has 4 columns: transid serial, userid char(30), callrec char(32), transstart datetime year to second, transend datetime year to second Each phone call will have a unique callrec, and a phone call can have multiple transactions. So I'm trying to get total transactions, total duration, average transaction duration, min transaction duration, max transaction duration, total calls and average call duration for a given userid for a given date range. Here's the query that I came up with SELECT COUNT(*) as total_transactions, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/3600 as total_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/count(*) as avg_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as min_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as max_trans_duration, COUNT(UNIQUE callrec) as total_calls, (SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) / COUNT(UNIQUE callrec)/60 as avg_call_duration FROM calltrans WHERE transstart BETWEEN ? AND ? AND userid = ? The statement works perfectly except for the last column for avg_call_duration. I get a syntax error on the COUNT(UNIQUE callrec) part of the statement. I can use COUNT(*) without any error, and I can use COUNT(ALL callrec) or COUNT(callrec) without error. I just can't use UNIQUE or DISTINCT with COUNT in the calculation. The COUNT(UNIQUE callrec) for total_calls works perfectly. I know I can do this in a stored procedure, but I really wanted to get this done with one statement. I read through the 11.50 SQL docs on aggregate functions, and unless I'm reading it wrong, it should work. Am I wrong or could this be a bug? <html> <body> <font size="1"> Teleformix Confidentiality Statement and Notice: This message and any associated files are covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521. This message and any attached content are for INTENDED RECEIPT ONLY. It may contain privileged or confidential information. If you have received this communication in error, please delete all information both electronic and hard copy. The information contained herein may contain information that is privileged or confidential, and may be subject to additional restrictions. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message is strictly prohibited. If you are the intended recipient or the employee or agent responsible for delivering this communication to the intended recipient, you are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication is strictly proh ibited. All attached documents and files are classified CONFIDENTIAL and for INTENDED RECIPIENT ONLY. If you need additional information or need to report any incidents please call 630.285.6500. Your attention in this matter is greatly appreciated. </font> </body> </html> **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. _____ avast! Antivirus <http://www.avast.com>; : Outbound message clean. Virus Database (VPS): 100409-1, 04/09/2010 Tested on: 4/9/2010 11:53:46 AM avast! - copyright (c) 1988-2010 ALWIL Software.
Still got a syntax error (SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) / COUNT(UNIQUE callrec))/60 # ^ # 201: A syntax error has occurred. # -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Friday, April 09, 2010 11:51 AM To: ids@iiug.org Subject: Re: SQL Query Syntax Question [19590] Try this coding for the last column: (SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) / COUNT(UNIQUE callrec))/60 as avg_call_duration I count a missing closing paranthesis, fixed above. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 9, 2010 at 12:34 PM, Jamie Gedye <jgedyedba@teleformix.com>wrote: > I have a kind of strange syntax error happening on a select statement, > and I need a little help. > > Engine 11.50FC6W2 - Red Hat Linux > > I have a table that tracks transactions of a telephone call. The table > has > 4 columns: > > transid serial, > userid char(30), > callrec char(32), > transstart datetime year to second, > transend datetime year to second > > Each phone call will have a unique callrec, and a phone call can have > multiple transactions. > > So I'm trying to get total transactions, total duration, average > transaction duration, min transaction duration, max transaction > duration, total calls and average call duration for a given userid for > a given date range. > > Here's the query that I came up with > > SELECT > COUNT(*) as total_transactions, > SUM(((transend - transstart)::interval second(9) to > second)::char(10)::int8)/3600 as total_trans_duration, SUM(((transend > - transstart)::interval second(9) to > second)::char(10)::int8)/count(*) as avg_trans_duration, > SUM(((transend - transstart)::interval second(9) to > second)::char(10)::int8) as min_trans_duration, SUM(((transend - > transstart)::interval second(9) to > second)::char(10)::int8) as max_trans_duration, COUNT(UNIQUE callrec) > as total_calls, (SUM(((transend - transstart)::interval second(9) to > second)::char(10)::int8) / COUNT(UNIQUE callrec)/60 as > avg_call_duration FROM calltrans WHERE transstart BETWEEN ? AND ? > AND userid = ? > > The statement works perfectly except for the last column for > avg_call_duration. > > I get a syntax error on the COUNT(UNIQUE callrec) part of the statement. > > I can use COUNT(*) without any error, and I can use COUNT(ALL callrec) > or > COUNT(callrec) without error. > > I just can't use UNIQUE or DISTINCT with COUNT in the calculation. The > COUNT(UNIQUE callrec) for total_calls works perfectly. > > I know I can do this in a stored procedure, but I really wanted to get > this done with one statement. > > I read through the 11.50 SQL docs on aggregate functions, and unless > I'm reading it wrong, it should work. > > Am I wrong or could this be a bug? > > <html> > <body> > <font size="1"> > Teleformix Confidentiality Statement and Notice: This message and any > associated files are covered by the Electronic Communications Privacy > Act, > 18 > U.S.C. 2510-2521. This message and any attached content are for > INTENDED RECEIPT ONLY. It may contain privileged or confidential > information. If you have received this communication in error, please > delete all information both electronic and hard copy. The information > contained herein may contain information that is privileged or > confidential, and may be subject to additional restrictions. If you > are not the intended recipient you are hereby notified that any > dissemination, copying or distribution of this message, or files > associated with this message is strictly prohibited. If you are the > intended recipient or the employee or agent responsible for delivering > this communication to the intended recipient, you are hereby notified > that any unauthorized use, dissemination, distribution or copying of > this communication is strictly proh ibited. All attached documents and > files are classified CONFIDENTIAL and for INTENDED RECIPIENT ONLY. If > you need additional information or need to report any incidents please > call 630.285.6500. Your attention in this matter is greatly > appreciated. > </font> > </body> > </html> > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016368e28d0f2f9e10483d0990b **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. <html> <body> <font size="1"> Teleformix Confidentiality Statement and Notice: This message and any associated files are covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521. This message and any attached content are for INTENDED RECEIPT ONLY. It may contain privileged or confidential information. If you have received this communication in error, please delete all information both electronic and hard copy. The information contained herein may contain information that is privileged or confidential, and may be subject to additional restrictions. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message is strictly prohibited. If you are the intended recipient or the employee or agent responsible for delivering this communication to the intended recipient, you are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication is strictly proh ibited. All attached documents and files are classified CONFIDENTIAL and for INTENDED RECIPIENT ONLY. If you need additional information or need to report any incidents please call 630.285.6500. Your attention in this matter is greatly appreciated. </font> </body> </html>
I counted five columns, if you include transid. Did you include the semi-colon at the end of the select statement? If that wasn't the problem, try isolating the problem by selecting only SELECT (SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) / COUNT(UNIQUE callrec)/60 as avg_call_duration FROM calltrans WHERE transstart BETWEEN ? AND ? AND userid = ?;
I know it's that column that's causing the problem. I already narrowed it down to that. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK@ FRANKCOMPUTER.COM Sent: Friday, April 09, 2010 12:03 PM To: ids@iiug.org Subject: Re: SQL Query Syntax Question [19593] I counted five columns, if you include transid. Did you include the semi-colon at the end of the select statement? If that wasn't the problem, try isolating the problem by selecting only SELECT (SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) / COUNT(UNIQUE callrec)/60 as avg_call_duration FROM calltrans WHERE transstart BETWEEN ? AND ? AND userid = ?; **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. <html> <body> <font size="1"> Teleformix Confidentiality Statement and Notice: This message and any associated files are covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521. This message and any attached content are for INTENDED RECEIPT ONLY. It may contain privileged or confidential information. If you have received this communication in error, please delete all information both electronic and hard copy. The information contained herein may contain information that is privileged or confidential, and may be subject to additional restrictions. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message is strictly prohibited. If you are the intended recipient or the employee or agent responsible for delivering this communication to the intended recipient, you are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication is strictly proh ibited. All attached documents and files are classified CONFIDENTIAL and for INTENDED RECIPIENT ONLY. If you need additional information or need to report any incidents please call 630.285.6500. Your attention in this matter is greatly appreciated. </font> </body> </html>
The argument of an aggregate function, for example, cannot itself contain an aggregate function. You cannot use aggregate functions in the following contexts: In a WHERE clause, unless it is contained in a subquery, or unless the aggregate is on a correlated column from a parent query and the WHERE clause is in a subquery within a HAVING clause As an argument to an aggregate function The following nested aggregate expression is invalid: MAX (AVG (order_num)) On a BYTE or TEXT column You cannot use a column that is a collection data type as an argument to the following aggregate functions: AVG SUM MIN MAX Expression or column arguments to built-in aggregates (except for COUNT, MAX, MIN, and RANGE) must return numeric or INTERVAL data types, but RANGE also accepts DATE and DATETIME arguments. For SUM and AVG, you cannot use the difference between two DATE values directly as the argument to an aggregate, but you can use DATE differences as operands within arithmetic expression arguments. For example: SELECT . . . AVG(ship_date - order_date)returns error -1201, but the following equivalent expression is valid: SELECT . . . AVG((ship_date - order_date)*1)
I'm not trying to use an aggregate within an aggregate. I'm just doing some division with aggregates To boil it down simply... sum(col1) / count(*) works sum(col1) / count(ALL col2) works sum(col1) / count(col2) works sum(col1) / count(unique col2) doesn't work sum(col1) / count(distinct col2) doesn't work count(unique col2) and count(distinct col2) are valid uses of the count aggregate. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK@ FRANKCOMPUTER.COM Sent: Friday, April 09, 2010 12:20 PM To: ids@iiug.org Subject: Re: RE: SQL Query Syntax Question [19595] The argument of an aggregate function, for example, cannot itself contain an aggregate function. You cannot use aggregate functions in the following contexts: In a WHERE clause, unless it is contained in a subquery, or unless the aggregate is on a correlated column from a parent query and the WHERE clause is in a subquery within a HAVING clause As an argument to an aggregate function The following nested aggregate expression is invalid: MAX (AVG (order_num)) On a BYTE or TEXT column You cannot use a column that is a collection data type as an argument to the following aggregate functions: AVG SUM MIN MAX Expression or column arguments to built-in aggregates (except for COUNT, MAX, MIN, and RANGE) must return numeric or INTERVAL data types, but RANGE also accepts DATE and DATETIME arguments. For SUM and AVG, you cannot use the difference between two DATE values directly as the argument to an aggregate, but you can use DATE differences as operands within arithmetic expression arguments. For example: SELECT . . . AVG(ship_date - order_date)returns error -1201, but the following equivalent expression is valid: SELECT . . . AVG((ship_date - order_date)*1) **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. <html> <body> <font size="1"> Teleformix Confidentiality Statement and Notice: This message and any associated files are covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521. This message and any attached content are for INTENDED RECEIPT ONLY. It may contain privileged or confidential information. If you have received this communication in error, please delete all information both electronic and hard copy. The information contained herein may contain information that is privileged or confidential, and may be subject to additional restrictions. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message is strictly prohibited. If you are the intended recipient or the employee or agent responsible for delivering this communication to the intended recipient, you are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication is strictly proh ibited. All attached documents and files are classified CONFIDENTIAL and for INTENDED RECIPIENT ONLY. If you need additional information or need to report any incidents please call 630.285.6500. Your attention in this matter is greatly appreciated. </font> </body> </html>
If I were you, I would play around with the parenthesis. Maybe it's not being correctly parsed, but you say the same exact statement works in SPL! Exactly where in the select statement is it pointing to the syntax error?
I can do the division in SPL by getting the 2 individual numbers with a SELECT and then doing the division. I was trying to keep from having to write a procedure. It's definitely the count(unique callrec) part that's getting the syntax error. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK@ FRANKCOMPUTER.COM Sent: Friday, April 09, 2010 1:06 PM To: ids@iiug.org Subject: Re: SQL Query Syntax Question [19597] If I were you, I would play around with the parenthesis. Maybe it's not being correctly parsed, but you say the same exact statement works in SPL! Exactly where in the select statement is it pointing to the syntax error? **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. <html> <body> <font size="1"> Teleformix Confidentiality Statement and Notice: This message and any associated files are covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521. This message and any attached content are for INTENDED RECEIPT ONLY. It may contain privileged or confidential information. If you have received this communication in error, please delete all information both electronic and hard copy. The information contained herein may contain information that is privileged or confidential, and may be subject to additional restrictions. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message is strictly prohibited. If you are the intended recipient or the employee or agent responsible for delivering this communication to the intended recipient, you are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication is strictly proh ibited. All attached documents and files are classified CONFIDENTIAL and for INTENDED RECIPIENT ONLY. If you need additional information or need to report any incidents please call 630.285.6500. Your attention in this matter is greatly appreciated. </font> </body> </html>
There are two uses of the COUNT function, and I don't think that's allowed. You can fake a non-distinct count using SUM and CASE, but I can't think of a way to fake a COUNT(DISTINCT...). Cheers, Dick Snoke Executive IT Specialist IBM Software Group - ChannelWorks Tel: (404) 487-1595 Email: dsnoke@us.ibm.com From: "Jamie Gedye" <jgedyedba@teleformix.com> To: ids@iiug.org Date: 04/09/10 12:36 PM Subject: SQL Query Syntax Question [19589] I have a kind of strange syntax error happening on a select statement, and I need a little help. Engine 11.50FC6W2 - Red Hat Linux I have a table that tracks transactions of a telephone call. The table has 4 columns: transid serial, userid char(30), callrec char(32), transstart datetime year to second, transend datetime year to second Each phone call will have a unique callrec, and a phone call can have multiple transactions. So I'm trying to get total transactions, total duration, average transaction duration, min transaction duration, max transaction duration, total calls and average call duration for a given userid for a given date range. Here's the query that I came up with SELECT COUNT(*) as total_transactions, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/3600 as total_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/count(*) as avg_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as min_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as max_trans_duration, COUNT(UNIQUE callrec) as total_calls, (SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) / COUNT(UNIQUE callrec)/60 as avg_call_duration FROM calltrans WHERE transstart BETWEEN ? AND ? AND userid = ? The statement works perfectly except for the last column for avg_call_duration. I get a syntax error on the COUNT(UNIQUE callrec) part of the statement. I can use COUNT(*) without any error, and I can use COUNT(ALL callrec) or COUNT(callrec) without error. I just can't use UNIQUE or DISTINCT with COUNT in the calculation. The COUNT(UNIQUE callrec) for total_calls works perfectly. I know I can do this in a stored procedure, but I really wanted to get this done with one statement. I read through the 11.50 SQL docs on aggregate functions, and unless I'm reading it wrong, it should work. Am I wrong or could this be a bug? <html> <body> <font size="1"> Teleformix Confidentiality Statement and Notice: This message and any associated files are covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521. This message and any attached content are for INTENDED RECEIPT ONLY. It may contain privileged or confidential information. If you have received this communication in error, please delete all information both electronic and hard copy. The information contained herein may contain information that is privileged or confidential, and may be subject to additional restrictions. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message is strictly prohibited. If you are the intended recipient or the employee or agent responsible for delivering this communication to the intended recipient, you are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication is strictly proh ibited. All attached documents and files are classified CONFIDENTIAL and for INTENDED RECIPIENT ONLY. If you need additional information or need to report any incidents please call 630.285.6500. Your attention in this matter is greatly appreciated. </font> </body> </html> ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
It looks like I can only use 1 occurrence of the count(unique callrec) within the query. If I remove the first one, then the one in the division calculation works. Any ideas on why that would be the case? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jamie Gedye Sent: Friday, April 09, 2010 1:20 PM To: ids@iiug.org Subject: RE: SQL Query Syntax Question [19598] I can do the division in SPL by getting the 2 individual numbers with a SELECT and then doing the division. I was trying to keep from having to write a procedure. It's definitely the count(unique callrec) part that's getting the syntax error. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK@ FRANKCOMPUTER.COM Sent: Friday, April 09, 2010 1:06 PM To: ids@iiug.org Subject: Re: SQL Query Syntax Question [19597] If I were you, I would play around with the parenthesis. Maybe it's not being correctly parsed, but you say the same exact statement works in SPL! Exactly where in the select statement is it pointing to the syntax error? **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. <html> <body> <font size="1"> Teleformix Confidentiality Statement and Notice: This message and any associated files are covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521. This message and any attached content are for INTENDED RECEIPT ONLY. It may contain privileged or confidential information. If you have received this communication in error, please delete all information both electronic and hard copy. The information contained herein may contain information that is privileged or confidential, and may be subject to additional restrictions. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message is strictly prohibited. If you are the intended recipient or the employee or agent responsible for delivering this communication to the intended recipient, you are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication is strictly proh ibited. All attached documents and files are classified CONFIDENTIAL and for INTENDED RECIPIENT ONLY. If you need additional information or need to report any incidents please call 630.285.6500. Your attention in this matter is greatly appreciated. </font> </body> </html> **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. <html> <body> <font size="1"> Teleformix Confidentiality Statement and Notice: This message and any associated files are covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521. This message and any attached content are for INTENDED RECEIPT ONLY. It may contain privileged or confidential information. If you have received this communication in error, please delete all information both electronic and hard copy. The information contained herein may contain information that is privileged or confidential, and may be subject to additional restrictions. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message is strictly prohibited. If you are the intended recipient or the employee or agent responsible for delivering this communication to the intended recipient, you are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication is strictly proh ibited. All attached documents and files are classified CONFIDENTIAL and for INTENDED RECIPIENT ONLY. If you need additional information or need to report any incidents please call 630.285.6500. Your attention in this matter is greatly appreciated. </font> </body> </html>
Jamie Gedye Wrote: >It looks like I can only use 1 occurrence of the count(unique callrec) >within the query. >If I remove the first one, then the one in the division calculation works. >Any ideas on why that would be the case? I can only guess it has something to do with implicit temp tables needed to produce a count(unique column). However, since all of your columns are aggregates, I think you can circumvent the issue by grabbing total_calls as a sub-select with matching criteria to the external SQL. Give this a try... SELECT COUNT(*) as total_transactions, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/3600 as total_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/count(*) as avg_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as min_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as max_trans_duration, (select COUNT(UNIQUE callrec) from calltrans where transstart between ? and ? and userid = ?) as total_calls, (SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) / COUNT(UNIQUE callrec)/60) as avg_call_duration FROM calltrans WHERE transstart BETWEEN ? AND ? AND userid = ? Good Luck, Dave Griffen
I still get the syntax error on the second occurrence of count unique. If I take out the sub select it works, so still only 1 occurrence looks to work. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVE GRIFFEN Sent: Friday, April 09, 2010 3:30 PM To: ids@iiug.org Subject: Re: RE: SQL Query Syntax Question [19601] Jamie Gedye Wrote: >It looks like I can only use 1 occurrence of the count(unique callrec) >within the query. >If I remove the first one, then the one in the division calculation works. >Any ideas on why that would be the case? I can only guess it has something to do with implicit temp tables needed to produce a count(unique column). However, since all of your columns are aggregates, I think you can circumvent the issue by grabbing total_calls as a sub-select with matching criteria to the external SQL. Give this a try... SELECT COUNT(*) as total_transactions, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/3600 as total_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/count(*) as avg_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as min_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as max_trans_duration, (select COUNT(UNIQUE callrec) from calltrans where transstart between ? and ? and userid = ?) as total_calls, (SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) / COUNT(UNIQUE callrec)/60) as avg_call_duration FROM calltrans WHERE transstart BETWEEN ? AND ? AND userid = ? Good Luck, Dave Griffen **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. <html> <body> <font size="1"> Teleformix Confidentiality Statement and Notice: This message and any associated files are covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521. This message and any attached content are for INTENDED RECEIPT ONLY. It may contain privileged or confidential information. If you have received this communication in error, please delete all information both electronic and hard copy. The information contained herein may contain information that is privileged or confidential, and may be subject to additional restrictions. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message is strictly prohibited. If you are the intended recipient or the employee or agent responsible for delivering this communication to the intended recipient, you are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication is strictly proh ibited. All attached documents and files are classified CONFIDENTIAL and for INTENDED RECIPIENT ONLY. If you need additional information or need to report any incidents please call 630.285.6500. Your attention in this matter is greatly appreciated. </font> </body> </html>
Jamie Gedye Wrote: >I still get the syntax error on the second occurrence of count unique. >If I take out the sub select it works, so still only 1 occurrence looks to work. Try shifting total_calls as a sub-select to be the last column... SELECT COUNT(*) as total_transactions, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/3600 as total_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/count(*) as avg_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as min_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as max_trans_duration, (SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) / COUNT(UNIQUE callrec)/60) , (select COUNT(UNIQUE callrec) from calltrans where transstart between ? and ? and userid = ?) as total_calls as avg_call_duration FROM calltrans WHERE transstart BETWEEN ? AND ? AND userid = ?
That worked! Any ideas why it wouldn't work the other way? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVE GRIFFEN Sent: Friday, April 09, 2010 3:49 PM To: ids@iiug.org Subject: Re: RE: RE: SQL Query Syntax Question [19603] Jamie Gedye Wrote: >I still get the syntax error on the second occurrence of count unique. >If I take out the sub select it works, so still only 1 occurrence looks >to work. Try shifting total_calls as a sub-select to be the last column... SELECT COUNT(*) as total_transactions, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/3600 as total_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8)/count(*) as avg_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as min_trans_duration, SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) as max_trans_duration, (SUM(((transend - transstart)::interval second(9) to second)::char(10)::int8) / COUNT(UNIQUE callrec)/60) , (select COUNT(UNIQUE callrec) from calltrans where transstart between ? and ? and userid = ?) as total_calls as avg_call_duration FROM calltrans WHERE transstart BETWEEN ? AND ? AND userid = ? **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. <html> <body> <font size="1"> Teleformix Confidentiality Statement and Notice: This message and any associated files are covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521. This message and any attached content are for INTENDED RECEIPT ONLY. It may contain privileged or confidential information. If you have received this communication in error, please delete all information both electronic and hard copy. The information contained herein may contain information that is privileged or confidential, and may be subject to additional restrictions. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message is strictly prohibited. If you are the intended recipient or the employee or agent responsible for delivering this communication to the intended recipient, you are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication is strictly proh ibited. All attached documents and files are classified CONFIDENTIAL and for INTENDED RECIPIENT ONLY. If you need additional information or need to report any incidents please call 630.285.6500. Your attention in this matter is greatly appreciated. </font> </body> </html>