MVSFORUMS.com :: View topic - SQL Query Help Needed?

through

#1: SQL Query Help Needed? Author: ramu_mohan21, Location: Bangalore, INDIA

Posted: Tue Apr 17, 2007 8:57 am
—
HI Board,
I have the data in Table-A(Fields: F1, F2, F3) and Table-B(Fields:Q1,Q2) like below:

Table-A
---------

Code:

F1 F2 F3
= === =====
A ABC ABCD
A XYZ XYZA
A PQR PQRS
B CCC CCCC
B DDD DDDD

Table-B
---------

Code:

Q1 Q2
== ======
A NNNNNNNN
B LLLLLLLLLLL
C JJJJJJJJJJJJ

I want to the data like below:

Code:

Q2 F1 Count
=========== == =====
NNNNNNNN A 3
LLLLLLLLLLL B 2

Count is the No of rows in Table-A for each type
Please anybody can help me in writing the SQL Query?

#2: Author: kolusu, Location: San Jose

Posted: Tue Apr 17, 2007 9:10 am
—
ramu_mohan21,

Unless I am missing something , this is a simple query. Try this untested solution

Code:

SELECT B.Q2
,A.F1
,COUNT(*)
FROM Table1 A
,Table2 B
WHERE A.F1 = B.Q1
GROUP BY B.Q2
,A.F1
;

Hope this helps...

Cheers

Kolusu

#3: Author: ramu_mohan21, Location: Bangalore, INDIA

Posted: Tue Apr 17, 2007 9:56 am
—
HI Kolusu,

I have already tried as you mentioned above. At that time I have below error:

DSNT408I SQLCODE = -122, ERROR: A SELECT STATEMENT WITH NO GROUP BY CLAUSE CONTAINS A COLUMN NAME AND AN AGGREGATE FUNCTION IN THE SELECT CLAUSE OR A COLUMN NAME IS CONTAINED IN THE SELECT CLAUSE BUT NOT IN THE GROUP BY CLAUSE

#4: Author: kolusu, Location: San Jose

Posted: Tue Apr 17, 2007 10:14 am
—
ramu_mohan21,

If you run the query as is then you will NOT get the -122 error. I am selecting 2 columns and those 2 columns are present in my GROUP by clause. You must be running a different query. Run the query as is shown above in spufi/qmf and shows us the error messages if you get any

Kolusu

#5: Author: ramu_mohan21, Location: Bangalore, INDIA

Posted: Wed Apr 18, 2007 1:02 am
—
You are right. I wrote a wrong SQL Query. I didn't keep B.Q2 in GROUP BY Clause. Now it is working fine.

Thank you for your immediate answer.

MVSFORUMS.com -> Database

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1