Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TKDoug
Starting Member
4 Posts |
 Posted - 2009-01-19 : 15:05:58
|
| I am fairly new to SQLPLUS and assigned created report lines from 2 tables, linked by an ID (not part of report) that has a 1 to many relationship. I am to produce summerized lines, grouped by date.The report is supposed to look like:Run Date|CTSS Count|Other01|Other02| ..... |Other11---------------------------------------------------Dec-11 | 12345| 457| 42421| ..... | 32122Dec-12 | 7890| 7654| 63444| ..... | 23456I can produce the lines individually, but as soon as I add GROUPED BY and SUM, things break down and I have no real experts to hit on.What I am currently attempting to make work is:select TO_CHAR(v.run_start_datetime,'MON-DD') as run_date, sum(ctss_total_in), sum(select o1.other_count from other_codes o1 where o1.volser_id = v.volser_id and o1.other_code = 1) as C1 sum(select o2.other_count from other_codes o2 where o2.volser_id = v.volser_id and o2.other_code = 1) as C2 sum(select o3.other_count from other_codes o3 where o3.volser_id = v.volser_id and o3.other_code = 1) as C3 from volser_data v where v.volser_name not like '_____W' group by TO_CHAR(v.run_start_datetime,'MON-DD') order by TO_CHAR(v.run_start_datetime,'MON-DD');Cna anybody help me fix this?J Douglas Perry |
|
|
TKDoug
Starting Member
4 Posts |
Posted - 2009-01-19 : 15:18:51
|
| Quick Correction - I look for o1.other_code = 1, o2.other_code = 2, etc. Also, I left out 4 thru 11 to save space.J Douglas Perry |
 |
|
|
Kumar_Anil
Yak Posting Veteran
68 Posts |
Posted - 2009-01-19 : 16:16:06
|
| Hello !It looks like the experts havent responded yet & to my naive eyes, this belongs to an Oracle forum. Anyways, I would change this as follows -regards,Anil Kumar.select TO_CHAR(v.run_start_datetime,'MON-DD') as run_date, ctss_total = C1.Other_counts + C2.Other_counts + ... , C1.Other_Counts, C2.Other_Counts,--from volser_data v ---join (select sum(o1.other_count) as Other_Counts from other_codes o1 where o1.other_code = 1)C1 on C1.volser_id = v.volser_id join (select sum(o2.other_count) as Other_Counts from other_codes o2 where o2.other_code = 1)C2 on C2.volser_id = v.volser_id -------where v.volser_name not like '%_____W'order by TO_CHAR(v.run_start_datetime,'MON-DD'); |
|
|
|
TKDoug
Starting Member
4 Posts |
Posted - 2009-01-19 : 18:43:41
|
| You are right that this is an Oracle database. I am brand new to this forum, as I am a long time mainframe programmer. I am now venturing into a mystery area and there doesn't seem to be anybody around me that has enough knowledge to know what I am talking about here.We do seen to not connect on at least part of this; the ctss_total is also supposed to be a SUM's field. I do thank you for the response and am trying to see if these JOIN's can be worked in.J Douglas Perry |
|
|
|
Kumar_Anil
Yak Posting Veteran
68 Posts |
Posted - 2009-01-19 : 18:57:03
|
| Hello !Yes, what Im trying to do in here was that I was summing up all of the Other_Counts & ctss_total is the name of the new column.Anyways, I did not understand what path your are taking here as whatever you wrote in here is pretty confusing especially when you are referncing the same table, " other_codes " & joining on the same columns with volser_id to the Volser_data table.Anyways, let me try another time, Can you please tell me 1. How does the output should look like & what I mean here is the CTSS Count Column = ??? Is it equal to Other01 + Other02 + ...+ Other112. What are the relevant columns in Other_codes table ? |
|
|
|
TKDoug
Starting Member
4 Posts |
Posted - 2009-01-20 : 11:09:11
|
| Well, I finally got this booger working. To answer my own question:select TO_CHAR(run_start_datetime,'MON-DD') as run_date, sum(CASE when o.other_code = 1 THEN v.ctss_total_in ELSE 0 END) as ctss_in_count, sum(CASE when o.other_code = 1 THEN o.other_count ELSE 0 END) as cnt_1, sum(CASE when o.other_code = 2 THEN o.other_count ELSE 0 END) as cnt_2, sum(CASE when o.other_code = 3 THEN o.other_count ELSE 0 END) as cnt_3, sum(CASE when o.other_code = 4 THEN o.other_count ELSE 0 END) as cnt_4, sum(CASE when o.other_code = 5 THEN o.other_count ELSE 0 END) as cnt_5, sum(CASE when o.other_code = 6 THEN o.other_count ELSE 0 END) as cnt_6, sum(CASE when o.other_code = 7 THEN o.other_count ELSE 0 END) as cnt_7, sum(CASE when o.other_code = 8 THEN o.other_count ELSE 0 END) as cnt_8, sum(CASE when o.other_code = 9 THEN o.other_count ELSE 0 END) as cnt_9, sum(CASE when o.other_code = 10 THEN o.other_count ELSE 0 END) as cnt_10, sum(CASE when o.other_code = 11 THEN o.other_count ELSE 0 END) as cnt_11 from volser_data v, other_codes o where v.volser_name not like '_____W' and v.volser_id = o.volser_id group by TO_CHAR(run_start_datetime,'MON-DD') order by TO_CHAR(run_start_datetime,'MON-DD');Thanks Kumar for your interest.J Douglas Perry |
|
|
|
|
|
|
|
|
Report lines from select,two tables,1 to many SUMs