Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sharad_shanu

New User


Joined: 03 Oct 2006
Posts: 40

PostPosted: Wed Jun 27, 2012 2:17 pm    Post subject: SQL query
Reply with quote

Hi all,

I have a DB2 table with following data.

Code:
State               City                     Amt1                 Amt2
Haryana          Gurgaon            1002.00             12547.21
Haryana          Faridabad          125.00               145.50
Tamilnadu       Chennai            125.25                1452.00
Tamilnadu       Trichy               1457.25              12544.00
Tamilnadu       Madurai             145.20               1245.52

Now I need output in below fashion:

Code:
Haryana          Sum(Amt1 of all Haryana city)         Sum(Amt1 of all Haryana city)
Gurgaon          Amt1 of Gurgaon                            Amt2 of Gurgaon
Faridabad        Amt1 of faridabad                           Amt2 of Faridabad
Tamilnadu       Sum(Amt1 of all Tamilnadu city)      Sum(Amt1 of all Tamilnadu city)
Chennai          Amt1 of Chennai                             Amt2 of Chennai
Trichy             Amt1 of Trichy                                Amt2 of Trichy
Madurai          Amt1 of madurai                             Amt2 of Madurai

I need to get this output through a SQL query. Not sure how to approach it. Suggestions?
Back to top
View user's profile Send private message

vicky10001
Warnings : 1

Active User


Joined: 13 Jul 2005
Posts: 136

PostPosted: Wed Jun 27, 2012 2:38 pm    Post subject:
Reply with quote

Select State, sum(Amt1), sum(Amt1) from table name.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 27, 2012 3:09 pm    Post subject:
Reply with quote

have no way to test it,
don't even know if the syntax is correct,
so you are going to have to test it yourself with spufi:

Code:

select state
      ,' '        as City
      ,sum(amt1)  as Amt1
      ,sum(amt2)  as Amt2
from table
union all
select state
      ,city       as City
      ,sum(amt1)  as Amt1
      ,sum(amt2)  as Amt2
from table

group by State, City
order by State, City
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK

PostPosted: Wed Jun 27, 2012 3:16 pm    Post subject:
Reply with quote

Hello sharad_shanu,

From what I understood from your post you can try on these lines-

Code:
SELECT STATE,CITY,SUM(AMT1),SUM(AMT2)
FROM TABLE
GROUP BY STATE,CITY


Quote:
Select State, sum(Amt1), sum(Amt1) from table name


You will get

Code:
SQLCODE = -122, ERROR:  COLUMN OR EXPRESSION IN THE SELECT LIST IS
NOT VALID
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 27, 2012 3:21 pm    Post subject:
Reply with quote

though it was difficult to determine,
I believe the TS wants

State totals
state city totals

but we will have to wait.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Wed Jun 27, 2012 5:55 pm    Post subject:
Reply with quote

Very difficult especially as after almost 6 years as a member here he has not learned how to use the code tags to display his data in a readable format.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jun 27, 2012 7:08 pm    Post subject: Reply to: SQL query
Reply with quote

The data is now coded, but it was "fiddled with" on the original post and so is not properly aligned. . .

d
Back to top
View user's profile Send private message
Santhosh Menon

New User


Joined: 19 Jun 2012
Posts: 4
Location: USA

PostPosted: Tue Jul 03, 2012 6:44 am    Post subject:
Reply with quote

Just a small tweak to the query by Dick Brenholtz ....(again have not run it in SPUFI)

but i think you want the summed up amount for state and individual amounts for city...

select state as place
,sum(amt1) as Amt1
,sum(amt2) as Amt2
from table
Group by STATE
union all
select
,city
,Amt1
,Amt2
from table

If you want summed up amounts for cities too... then add GROUP by CITY for the second query...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jul 04, 2012 7:18 am    Post subject:
Reply with quote

Menon,
Even though the TS wanted state and city under the same heading,
i ignored his request
for the obvious reason
that it would require knowledge of all the states and cities
to know what is what.
but,
if the TS wants it his way,
he has your way.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us