newbie GROUP BY question

FAQ

Memberlist

Usergroups

Profile

Preferences

· nixdoc.net

· man pages

· Linux HOWTOs

· FreeBSD Tips

· Forums

Forum index » Databases » IBM DB2

newbie GROUP BY question

Page 1 of 1 [7 Posts]

View previous topic :: View next topic

Author

Message

Knut Stolze
*nix forums Guru

Joined: 28 Jul 2005
Posts: 755

Posted: Tue Feb 15, 2005 6:51 am Post subject: Note

Re: newbie GROUP BY question

Mamma Mia wrote:

Quote:

"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:cuqet5$dnj$1@fsuj29.rz.uni-jena.de...
Mamma Mia wrote:

You want to have the "HOUR(SLUTT_TIDSPKT_TMS)" values to define your
groups.
And for each group you want to calculate the average. So don't include
the
AVG() calculation in your group definition:

SELECT AVG(svar_tid)/1000, HOUR(slutt_tidspkt_tms)
FROM m3.tran_stat
GROUP BY HOUR(slutt_tidspkt_tms)

I tried your suggestion, but got SQL0104N. I now got a very strong feeling
that running on
DB2 OS/390 7.1.2 might be part of the problem?

Maybe. I don't know DB2 for z/OS (OS/390) well enough to comment. The
exact error message and the table definition would help, though.

Quote:

My lesson so far (please correct me):

1. You cannot use an aggregate function or any function in the GROUP
BY-clase (see first test)

Right.

Not entirely right, since you permit HOUR in your GROUP BY clause. Doesn't
seem to work
with me though.

I missed the part "of any function" on first reading. I would be really
surprised if functions were not allowed in GROUP BY. Therefore, I guess
your problem lies elsewhere.

Quote:

3. Aliasing is a must, when grouping AVG/COUNT (e.g. functions)

Wrong.

Again, I suspect that DB2 7.1.2 have something to do with this. Which
version are you running?

I'm using DB2 V8.2 for LUW. However, the things we talk about here are
basic SQL issues and I'm fairly certain that they are addressed for many
years already. So let's find the real problem.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Mamma Mia
*nix forums beginner

Joined: 11 Feb 2005
Posts: 3

Posted: Mon Feb 14, 2005 2:29 pm Post subject: Note

Re: newbie GROUP BY question

"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:cuqet5$dnj$1@fsuj29.rz.uni-jena.de...

Quote:

Mamma Mia wrote:

You want to have the "HOUR(SLUTT_TIDSPKT_TMS)" values to define your
groups.
And for each group you want to calculate the average. So don't include
the
AVG() calculation in your group definition:

SELECT AVG(svar_tid)/1000, HOUR(slutt_tidspkt_tms)
FROM m3.tran_stat
GROUP BY HOUR(slutt_tidspkt_tms)

I tried your suggestion, but got SQL0104N. I now got a very strong feeling
that running on
DB2 OS/390 7.1.2 might be part of the problem?

Besides, I'm programming a SQL-Builder, to make lots of various SQL for
reports on this table.
Therefore I would like to make the reverse query, even if that doesn't make
a whole lot of
sense (better with a senseless report, than an SQL-error...) . Thus I regard
this solution as a
special case. A better exmple for "difficult" query might be "All
transaction-ids pr average response time".

Quote:

My lesson so far (please correct me):

1. You cannot use an aggregate function or any function in the GROUP
BY-clase (see first test)

Right.

Not entirely right, since you permit HOUR in your GROUP BY clause. Doesn't
seem to work
with me though.

Quote:

2. Aliasing in this context is only useful with nested SELECTs (see
second
test)

Actually, you don't need aliasing at all. In most cases, you only need it
for applications and not within the query.

3. Aliasing is a must, when grouping AVG/COUNT (e.g. functions)

Wrong.

Again, I suspect that DB2 7.1.2 have something to do with this. Which
version are you running?

Quote:

5. I have to add "AS TABLE_TMP" and "AS TABLE_TMP2" to make the SQL
processing. I have no idea why.

No, you don't need that at all.

DB2-version?

Best regards
Morten Simonsen

Knut Stolze
*nix forums Guru

Joined: 28 Jul 2005
Posts: 755

Posted: Mon Feb 14, 2005 2:08 pm Post subject: Note

Re: newbie GROUP BY question

Mamma Mia wrote:

Quote:

----- Original Message -----
From: "Thiru" <WantedToBeDBA@gmail.com
Newsgroups: comp.databases.ibm-db2
Sent: Monday, February 14, 2005 7:45 AM
Subject: Re: newbie GROUP BY question

If problem doesn't solve, Send the full query.

Ok. I have a table which contains info about transactions. The table
M3.TRAN_STAT contains:
TR_KDE = A transaction-code
SVAR_TID = The response time of the transaction in ms
SLUTT_TIDSPKT_TMS = A DB2-timestamp indicating when the transaction
ended.

Let's say I want to make a SQL-query which lists the average response time
(in seconds) pr hour.

1. First a naive approach (I'm a newbie!....and wouldn't it be nice if it
worked?)
SELECT AVG(SVAR_TID)/1000, HOUR(SLUTT_TIDSPKT_TMS)
FROM M3.TRAN_STAT
GROUP BY AVG(SVAR_TID)/1000, HOUR(SLUTT_TIDSPKT_TMS)
return SQL0104N.

You want to have the "HOUR(SLUTT_TIDSPKT_TMS)" values to define your groups.
And for each group you want to calculate the average. So don't include the
AVG() calculation in your group definition:

SELECT AVG(svar_tid)/1000, HOUR(slutt_tidspkt_tms)
FROM m3.tran_stat
GROUP BY HOUR(slutt_tidspkt_tms)

Quote:

4. I am slightly annoyed, my simple request is turning into unreadable
gibberish! But, I never give up:)
SELECT AVG_TMP, HOUR_TMP FROM
(SELECT AVG(SVAR_TID)/1000 AS AVG_TMP, HOUR_TMP FROM
(SELECT SVAR_TID, HOUR(SLUTT_TIDSPKT_TMS) AS HOUR_TMP
FROM M3.TRAN_STAT)
AS TABLE_TMP
GROUP BY HOUR_TMP)

Here you do the right thing: you say that the groups are identified by
HOUR_TMP only.

Quote:

AS TABLE_TMP2
GROUP BY AVG_TMP, HOUR_TMP

return a correct answer!!

My lesson so far (please correct me):

1. You cannot use an aggregate function or any function in the GROUP
BY-clase (see first test)

Right.

Quote:

2. Aliasing in this context is only useful with nested SELECTs (see second
test)

Actually, you don't need aliasing at all. In most cases, you only need it
for applications and not within the query.

Quote:

3. Aliasing is a must, when grouping AVG/COUNT (e.g. functions)

Wrong.

Quote:

4. AVG (or more generally: column-functions) in a SELECT-clause, demands a
GROUP BY-clause. Exception: If the SELECT contains ONLY column-functions.
(see third test)

Correct. The reason is that the database system needs to know what it shall
do with all the other columns in the select list. So usually, those other
columns are used to define the groups for the aggregation done by the
column functions.

Quote:

5. I have to add "AS TABLE_TMP" and "AS TABLE_TMP2" to make the SQL
processing. I have no idea why.

No, you don't need that at all.

Quote:

In sum, these rules transform into the fourth test. Am I right? Any
suggestions for a simpler approach?

No, your 4th test worked because you made another mistake, which turned out
to be the right one. ;-)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Mamma Mia
*nix forums beginner

Joined: 11 Feb 2005
Posts: 3

Posted: Mon Feb 14, 2005 12:32 pm Post subject: Note

Re: newbie GROUP BY question

----- Original Message -----
From: "Thiru" <WantedToBeDBA@gmail.com>
Newsgroups: comp.databases.ibm-db2
Sent: Monday, February 14, 2005 7:45 AM
Subject: Re: newbie GROUP BY question

Quote:

If problem doesn't solve, Send the full query.

Ok. I have a table which contains info about transactions. The table
M3.TRAN_STAT contains:
TR_KDE = A transaction-code
SVAR_TID = The response time of the transaction in ms
SLUTT_TIDSPKT_TMS = A DB2-timestamp indicating when the transaction
ended.

Let's say I want to make a SQL-query which lists the average response time
(in seconds) pr hour.

1. First a naive approach (I'm a newbie!....and wouldn't it be nice if it
worked?)
SELECT AVG(SVAR_TID)/1000, HOUR(SLUTT_TIDSPKT_TMS)
FROM M3.TRAN_STAT
GROUP BY AVG(SVAR_TID)/1000, HOUR(SLUTT_TIDSPKT_TMS)
return SQL0104N.

2. Ok, I try to add aliasing, so the GROUP BY-statement does not contain
function-statements
SELECT AVG(SVAR_TID)/1000 AS AVG_TMP, HOUR(SLUTT_TIDSPKT_TMS) AS
HOUR_TMP
FROM M3.TRAN_STAT
GROUP BY AVG_TMP, HOUR_TMP
return SQL0206N (complaining about AVG_TMP *and* HOUR_TMP)

3. Well, then: Time to get radical.
SELECT AVG_TMP, HOUR_TMP FROM
(SELECT AVG(SVAR_TID)/1000 AS AVG_TMP, HOUR(SLUTT_TIDSPKT_TMS) AS
HOUR_TMP
FROM M3.TRAN_STAT) AS TABLE_TMP) AS TABLE_TMP
GROUP BY AVG_TMP, HOUR_TMP
return SQL0122N (obviously the sub-select must contain a GROUP BY, but the
above examples defies that option!?)

4. I am slightly annoyed, my simple request is turning into unreadable
gibberish! But, I never give up:)
SELECT AVG_TMP, HOUR_TMP FROM
(SELECT AVG(SVAR_TID)/1000 AS AVG_TMP, HOUR_TMP FROM
(SELECT SVAR_TID, HOUR(SLUTT_TIDSPKT_TMS) AS HOUR_TMP
FROM M3.TRAN_STAT)
AS TABLE_TMP
GROUP BY HOUR_TMP)
AS TABLE_TMP2
GROUP BY AVG_TMP, HOUR_TMP

return a correct answer!!

My lesson so far (please correct me):

1. You cannot use an aggregate function or any function in the GROUP
BY-clase (see first test)
2. Aliasing in this context is only useful with nested SELECTs (see second
test)
3. Aliasing is a must, when grouping AVG/COUNT (e.g. functions)
4. AVG (or more generally: column-functions) in a SELECT-clause, demands a
GROUP BY-clause. Exception: If the SELECT contains ONLY column-functions.
(see third test)
5. I have to add "AS TABLE_TMP" and "AS TABLE_TMP2" to make the SQL
processing. I have no idea why.

In sum, these rules transform into the fourth test. Am I right? Any
suggestions for a simpler approach?

Thanks for any suggestions

Morten Simonsen

Thiru
*nix forums addict

Joined: 20 May 2005
Posts: 80

Posted: Mon Feb 14, 2005 5:45 am Post subject: Re: newbie GROUP BY question

Hi, Some aggregate function has to be used in select clause. If problem doesn't solve, Send the full query. Cheers, Thiru WantedToBeDBA.

Vincent M
*nix forums Guru

Joined: 22 Feb 2005
Posts: 1332

Posted: Fri Feb 11, 2005 1:24 pm Post subject: Note

Re: newbie GROUP BY question

"Mamma Mia" <mamma.mia@abba.com> wrote in message
news:1108130856.c13a9badca6de3dfd48dcfb37887b74f@teranews...

Quote:

Hi

I want to write

SELECT something FROM table GROUP BY hour(timestamp)

where "something" definitely has to contain "hour(timestamp)".

I get an error SQL0104N.

Any suggestions?

Morten Simonsen

Please post the SQL statement exactly as you submitted it.

Keep in mind that you must select the columns in the group by clause.

Mamma Mia
*nix forums beginner

Joined: 11 Feb 2005
Posts: 3

Posted: Fri Feb 11, 2005 1:07 pm Post subject: newbie GROUP BY question

Hi I want to write SELECT something FROM table GROUP BY hour(timestamp) where "something" definitely has to contain "hour(timestamp)". I get an error SQL0104N. Any suggestions? Morten Simonsen

Google

Display posts from previous:

Page 1 of 1 [7 Posts]

View previous topic :: View next topic

The time now is Thu Jan 08, 2009 3:29 am | All times are GMT

Forum index » Databases » IBM DB2

Jump to:

Topic		Author	Forum	Replies	Last Post
Similar Topics
	Newbie question: How to forward a domain to a mailbox?	leei	Postfix	0	Fri Aug 24, 2007 4:55 pm
	configuration question for httpd	Karl Wang	Apache	1	Fri Jul 21, 2006 2:10 pm
	nim problem/question	Ron	AIX	0	Fri Jul 21, 2006 1:57 pm
	question for JAVA developer who r using postgres sql as b...	deepak pal	PostgreSQL	1	Fri Jul 21, 2006 9:00 am
	Encryption Question	dtuttle1@gmail.com	Berkeley DB	2	Thu Jul 20, 2006 10:09 pm

Credit Cards | School Girl Costumes | Credit Card Shop | Credit Cards | Mortgage

Other DeniX Solutions sites: Unix/Linux blog | electronics forum | medicine forum | science forum |

[ Time: 0.4108s ][ Queries: 20 (0.3042s) ][ GZIP on - Debug on ]