Home » SQL & PL/SQL » SQL & PL/SQL » HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table
HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table [message #237726] Tue, 15 May 2007 09:32 Go to next message
svgeorge
Messages: 6
Registered: May 2007
Location: Feasterville, PA
Junior Member
Here is the query for the view i am creating:

select ifd_tester.time_stamp,ifd_Tester.serial_number,ifd_tester.model_number,
ifd_tester.assembly_line,ifd_tester.result,ifd_tester.test_current,ifd_tester.test_voltage,
"IFD_TESTER"."TESTNUMBER", "IFD_TESTER"."DATA0", "IFD_TESTER"."DATA1", "IFD_TESTER"."DATA2", "IFD_TESTER"."DATA3", "IFD_TESTER"."DATA4", "IFD_TESTER"."DATA5", "IFD_TESTER"."DATA6", "IFD_TESTER"."DATA7", "IFD_TESTER"."DATA8", "IFD_TESTER"."DATA9", "IFD_TESTER"."DATA10", "IFD_TESTER"."DATA11", "IFD_TESTER"."DATA12", "IFD_TESTER"."DATA13", "IFD_TESTER"."DATA14", "IFD_TESTER"."DATA15", "IFD_TESTER"."DATA16", "IFD_TESTER"."DATA17", "IFD_TESTER"."DATA18", "IFD_TESTER"."DATA19", "IFD_TESTER"."DATA20", "IFD_TESTER"."DATA21", "IFD_TESTER"."DATA22", "IFD_TESTER"."DATA23", "IFD_TESTER"."DATA24", "IFD_TESTER"."DATA25", "IFD_TESTER"."DATA26", "IFD_TESTER"."DATA27", "IFD_TESTER"."DATA28", "IFD_TESTER"."DATA29", "IFD_TESTER"."DATA30", "IFD_TESTER"."DATA31", "IFD_TESTER"."DATA32", "IFD_TESTER"."DATA33", "IFD_TESTER"."DATA34", "IFD_TESTER"."DATA35", "IFD_TESTER"."DATA36", "IFD_TESTER"."DATA37", "IFD_TESTER"."DATA38", "IFD_TESTER"."DATA39", "IFD_TESTER"."DATA40", "IFD_TESTER"."DATA41", "IFD_TESTER"."DATA42", "IFD_TESTER"."DATA43", "IFD_TESTER"."DATA44", "IFD_TESTER"."DATA45", "IFD_TESTER"."DATA46", "IFD_TESTER"."DATA47", "IFD_TESTER"."DATA48", "IFD_TESTER"."DATA49", "IFD_TESTER"."DATA50", "IFD_TESTER"."DATA51", "IFD_TESTER"."DATA52", "IFD_TESTER"."DATA53", "IFD_TESTER"."DATA54", "IFD_TESTER"."DATA55", "IFD_TESTER"."DATA56", "IFD_TESTER"."DATA57", "IFD_TESTER"."DATA58", "IFD_TESTER"."DATA59", "IFD_TESTER"."DATA60", "IFD_TESTER"."DATA61", "IFD_TESTER"."DATA62", "IFD_TESTER"."DATA63", "IFD_TESTER"."DATA64", "IFD_TESTER"."DATA65", "IFD_TESTER"."DATA66", "IFD_TESTER"."DATA67", "IFD_TESTER"."DATA68", "IFD_TESTER"."DATA69", "IFD_TESTER"."DATA70", "IFD_TESTER"."DATA71", "IFD_TESTER"."DATA72", "IFD_TESTER"."DATA73", "IFD_TESTER"."DATA74", "IFD_TESTER"."DATA75", "IFD_TESTER"."DATA76", "IFD_TESTER"."DATA77", "IFD_TESTER"."DATA78", "IFD_TESTER"."DATA79", "IFD_TESTER"."DATA80", "IFD_TESTER"."DATA81", "IFD_TESTER"."DATA82", "IFD_TESTER"."DATA83", "IFD_TESTER"."DATA84", "IFD_TESTER"."DATA85", "IFD_TESTER"."DATA86", "IFD_TESTER"."DATA87", "IFD_TESTER"."DATA88", "IFD_TESTER"."DATA89", "IFD_TESTER"."DATA90", "IFD_TESTER"."DATA91", "IFD_TESTER"."DATA92", "IFD_TESTER"."DATA93", "IFD_TESTER"."DATA94", "IFD_TESTER"."DATA95", "IFD_TESTER"."DATA96", "IFD_TESTER"."DATA97", "IFD_TESTER"."DATA98", "IFD_TESTER"."DATA99", "IFD_TESTER"."DATA100", "IFD_TESTER"."DATA101", "IFD_TESTER"."DATA102", "IFD_TESTER"."DATA103", "IFD_TESTER"."DATA104", "IFD_TESTER"."DATA105", "IFD_TESTER"."DATA106", "IFD_TESTER"."DATA107", "IFD_TESTER"."DATA108", "IFD_TESTER"."DATA109", "IFD_TESTER"."DATA110", "IFD_TESTER"."DATA111", "IFD_TESTER"."DATA112", "IFD_TESTER"."DATA113", "IFD_TESTER"."DATA114", "IFD_TESTER"."DATA115", "IFD_TESTER"."DATA116", "IFD_TESTER"."DATA117", "IFD_TESTER"."DATA118", "IFD_TESTER"."DATA119", "IFD_TESTER"."DATA120", "IFD_TESTER"."DATA121", "IFD_TESTER"."DATA122", "IFD_TESTER"."DATA123", "IFD_TESTER"."DATA124", "IFD_TESTER"."DATA125", "IFD_TESTER"."DATA126", "IFD_TESTER"."DATA127", "IFD_TESTER"."DATA128", "IFD_TESTER"."DATA129", "IFD_TESTER"."DATA130", "IFD_TESTER"."DATA131", "IFD_TESTER"."DATA132", "IFD_TESTER"."DATA133", "IFD_TESTER"."DATA134", "IFD_TESTER"."DATA135", "IFD_TESTER"."DATA136", "IFD_TESTER"."DATA137", "IFD_TESTER"."DATA138", "IFD_TESTER"."DATA139", "IFD_TESTER"."DATA140", "IFD_TESTER"."DATA141", "IFD_TESTER"."DATA142", "IFD_TESTER"."DATA143", "IFD_TESTER"."DATA144", "IFD_TESTER"."DATA145", "IFD_TESTER"."DATA146", "IFD_TESTER"."DATA147", "IFD_TESTER"."DATA148", "IFD_TESTER"."DATA149", "IFD_TESTER"."DATA150", "IFD_TESTER"."DATA151", "IFD_TESTER"."DATA152", "IFD_TESTER"."DATA153", "IFD_TESTER"."DATA154", "IFD_TESTER"."DATA155",
Sum(DATA0, DATA1, DATA2, DATA3, DATA4, DATA5, DATA6, DATA7, DATA8, DATA9, DATA10, DATA11, DATA12, DATA13, DATA14, DATA15, DATA16, DATA17, DATA18, DATA19, DATA20, DATA21, DATA22, DATA23, DATA24, DATA25, DATA26, DATA27, DATA28, DATA29, DATA30, DATA31, DATA32, DATA33, DATA34, DATA35, DATA36, DATA37, DATA38, DATA39, DATA40, DATA41, DATA42, DATA43, DATA44, DATA45, DATA46, DATA47, DATA48, DATA49, DATA50, DATA51, DATA52, DATA53, DATA54, DATA55, DATA56, DATA57, DATA58, DATA59, DATA60, DATA61, DATA62, DATA63, DATA64, DATA65, DATA66, DATA67, DATA68, DATA69, DATA70, DATA71, DATA72, DATA73, DATA74, DATA75, DATA76, DATA77, DATA78, DATA79, DATA80, DATA81, DATA82, DATA83, DATA84, DATA85, DATA86, DATA87, DATA88, DATA89, DATA90, DATA91, DATA92, DATA93, DATA94, DATA95, DATA96, DATA97, DATA98, DATA99, DATA100, DATA101, DATA102, DATA103, DATA104, DATA105, DATA106, DATA107, DATA108, DATA109, DATA110, DATA111, DATA112, DATA113, DATA114, DATA115, DATA116, DATA117, DATA118, DATA119, DATA120, DATA121, DATA122, DATA123, DATA124, DATA125, DATA126, DATA127, DATA128, DATA129, DATA130, DATA131, DATA132, DATA133, DATA134, DATA135, DATA136, DATA137, DATA138, DATA139, DATA140, DATA141, DATA142, DATA143, DATA144, DATA145, DATA146, DATA147, DATA148, DATA149, DATA150, DATA151, DATA152, DATA153, DATA154, DATA155)as "SUM"
from ifd_tester

[Updated on: Tue, 15 May 2007 09:59]

Report message to a moderator

Re: HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table [message #237729 is a reply to message #237726] Tue, 15 May 2007 09:41 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone help me please urgently!!!
Please explain why it is urgent for ME to solve this problem for you?

>HOW TO SUM , AVERAGE , COUNT (only negative values -1 or -2)(Column data0to data155)
Please read & following the posting guidelines as stated in the #1 STICKY post at the top of this forum.
Re: HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table [message #237741 is a reply to message #237729] Tue, 15 May 2007 10:01 Go to previous messageGo to next message
svgeorge
Messages: 6
Registered: May 2007
Location: Feasterville, PA
Junior Member
Here is the query for the view i am creating:
select ifd_tester.time_stamp,ifd_Tester.serial_number,ifd_tester.model_number,
ifd_tester.assembly_line,ifd_tester.result,ifd_tester.test_current,ifd_tester.test_voltage,
"IFD_TESTER"."TESTNUMBER", "IFD_TESTER"."DATA0", "IFD_TESTER"."DATA1", "IFD_TESTER"."DATA2", "IFD_TESTER"."DATA3", "IFD_TESTER"."DATA4", "IFD_TESTER"."DATA5", "IFD_TESTER"."DATA6", "IFD_TESTER"."DATA7", "IFD_TESTER"."DATA8", "IFD_TESTER"."DATA9", "IFD_TESTER"."DATA10", "IFD_TESTER"."DATA11", "IFD_TESTER"."DATA12", "IFD_TESTER"."DATA13", "IFD_TESTER"."DATA14", "IFD_TESTER"."DATA15", "IFD_TESTER"."DATA16", "IFD_TESTER"."DATA17", "IFD_TESTER"."DATA18", "IFD_TESTER"."DATA19", "IFD_TESTER"."DATA20", "IFD_TESTER"."DATA21", "IFD_TESTER"."DATA22", "IFD_TESTER"."DATA23", "IFD_TESTER"."DATA24", "IFD_TESTER"."DATA25", "IFD_TESTER"."DATA26", "IFD_TESTER"."DATA27", "IFD_TESTER"."DATA28", "IFD_TESTER"."DATA29", "IFD_TESTER"."DATA30", "IFD_TESTER"."DATA31", "IFD_TESTER"."DATA32", "IFD_TESTER"."DATA33", "IFD_TESTER"."DATA34", "IFD_TESTER"."DATA35", "IFD_TESTER"."DATA36", "IFD_TESTER"."DATA37", "IFD_TESTER"."DATA38", "IFD_TESTER"."DATA39", "IFD_TESTER"."DATA40", "IFD_TESTER"."DATA41", "IFD_TESTER"."DATA42", "IFD_TESTER"."DATA43", "IFD_TESTER"."DATA44", "IFD_TESTER"."DATA45", "IFD_TESTER"."DATA46", "IFD_TESTER"."DATA47", "IFD_TESTER"."DATA48", "IFD_TESTER"."DATA49", "IFD_TESTER"."DATA50", "IFD_TESTER"."DATA51", "IFD_TESTER"."DATA52", "IFD_TESTER"."DATA53", "IFD_TESTER"."DATA54", "IFD_TESTER"."DATA55", "IFD_TESTER"."DATA56", "IFD_TESTER"."DATA57", "IFD_TESTER"."DATA58", "IFD_TESTER"."DATA59", "IFD_TESTER"."DATA60", "IFD_TESTER"."DATA61", "IFD_TESTER"."DATA62", "IFD_TESTER"."DATA63", "IFD_TESTER"."DATA64", "IFD_TESTER"."DATA65", "IFD_TESTER"."DATA66", "IFD_TESTER"."DATA67", "IFD_TESTER"."DATA68", "IFD_TESTER"."DATA69", "IFD_TESTER"."DATA70", "IFD_TESTER"."DATA71", "IFD_TESTER"."DATA72", "IFD_TESTER"."DATA73", "IFD_TESTER"."DATA74", "IFD_TESTER"."DATA75", "IFD_TESTER"."DATA76", "IFD_TESTER"."DATA77", "IFD_TESTER"."DATA78", "IFD_TESTER"."DATA79", "IFD_TESTER"."DATA80", "IFD_TESTER"."DATA81", "IFD_TESTER"."DATA82", "IFD_TESTER"."DATA83", "IFD_TESTER"."DATA84", "IFD_TESTER"."DATA85", "IFD_TESTER"."DATA86", "IFD_TESTER"."DATA87", "IFD_TESTER"."DATA88", "IFD_TESTER"."DATA89", "IFD_TESTER"."DATA90", "IFD_TESTER"."DATA91", "IFD_TESTER"."DATA92", "IFD_TESTER"."DATA93", "IFD_TESTER"."DATA94", "IFD_TESTER"."DATA95", "IFD_TESTER"."DATA96", "IFD_TESTER"."DATA97", "IFD_TESTER"."DATA98", "IFD_TESTER"."DATA99", "IFD_TESTER"."DATA100", "IFD_TESTER"."DATA101", "IFD_TESTER"."DATA102", "IFD_TESTER"."DATA103", "IFD_TESTER"."DATA104", "IFD_TESTER"."DATA105", "IFD_TESTER"."DATA106", "IFD_TESTER"."DATA107", "IFD_TESTER"."DATA108", "IFD_TESTER"."DATA109", "IFD_TESTER"."DATA110", "IFD_TESTER"."DATA111", "IFD_TESTER"."DATA112", "IFD_TESTER"."DATA113", "IFD_TESTER"."DATA114", "IFD_TESTER"."DATA115", "IFD_TESTER"."DATA116", "IFD_TESTER"."DATA117", "IFD_TESTER"."DATA118", "IFD_TESTER"."DATA119", "IFD_TESTER"."DATA120", "IFD_TESTER"."DATA121", "IFD_TESTER"."DATA122", "IFD_TESTER"."DATA123", "IFD_TESTER"."DATA124", "IFD_TESTER"."DATA125", "IFD_TESTER"."DATA126", "IFD_TESTER"."DATA127", "IFD_TESTER"."DATA128", "IFD_TESTER"."DATA129", "IFD_TESTER"."DATA130", "IFD_TESTER"."DATA131", "IFD_TESTER"."DATA132", "IFD_TESTER"."DATA133", "IFD_TESTER"."DATA134", "IFD_TESTER"."DATA135", "IFD_TESTER"."DATA136", "IFD_TESTER"."DATA137", "IFD_TESTER"."DATA138", "IFD_TESTER"."DATA139", "IFD_TESTER"."DATA140", "IFD_TESTER"."DATA141", "IFD_TESTER"."DATA142", "IFD_TESTER"."DATA143", "IFD_TESTER"."DATA144", "IFD_TESTER"."DATA145", "IFD_TESTER"."DATA146", "IFD_TESTER"."DATA147", "IFD_TESTER"."DATA148", "IFD_TESTER"."DATA149", "IFD_TESTER"."DATA150", "IFD_TESTER"."DATA151", "IFD_TESTER"."DATA152", "IFD_TESTER"."DATA153", "IFD_TESTER"."DATA154", "IFD_TESTER"."DATA155",
Sum(DATA0, DATA1, DATA2, DATA3, DATA4, DATA5, DATA6, DATA7, DATA8, DATA9, DATA10, DATA11, DATA12, DATA13, DATA14, DATA15, DATA16, DATA17, DATA18, DATA19, DATA20, DATA21, DATA22, DATA23, DATA24, DATA25, DATA26, DATA27, DATA28, DATA29, DATA30, DATA31, DATA32, DATA33, DATA34, DATA35, DATA36, DATA37, DATA38, DATA39, DATA40, DATA41, DATA42, DATA43, DATA44, DATA45, DATA46, DATA47, DATA48, DATA49, DATA50, DATA51, DATA52, DATA53, DATA54, DATA55, DATA56, DATA57, DATA58, DATA59, DATA60, DATA61, DATA62, DATA63, DATA64, DATA65, DATA66, DATA67, DATA68, DATA69, DATA70, DATA71, DATA72, DATA73, DATA74, DATA75, DATA76, DATA77, DATA78, DATA79, DATA80, DATA81, DATA82, DATA83, DATA84, DATA85, DATA86, DATA87, DATA88, DATA89, DATA90, DATA91, DATA92, DATA93, DATA94, DATA95, DATA96, DATA97, DATA98, DATA99, DATA100, DATA101, DATA102, DATA103, DATA104, DATA105, DATA106, DATA107, DATA108, DATA109, DATA110, DATA111, DATA112, DATA113, DATA114, DATA115, DATA116, DATA117, DATA118, DATA119, DATA120, DATA121, DATA122, DATA123, DATA124, DATA125, DATA126, DATA127, DATA128, DATA129, DATA130, DATA131, DATA132, DATA133, DATA134, DATA135, DATA136, DATA137, DATA138, DATA139, DATA140, DATA141, DATA142, DATA143, DATA144, DATA145, DATA146, DATA147, DATA148, DATA149, DATA150, DATA151, DATA152, DATA153, DATA154, DATA155)as "SUM"
from ifd_tester
Re: HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table [message #237743 is a reply to message #237741] Tue, 15 May 2007 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you repeat the same post?
What is the question?

Regards
Michel
Re: HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table [message #237747 is a reply to message #237743] Tue, 15 May 2007 10:11 Go to previous messageGo to next message
svgeorge
Messages: 6
Registered: May 2007
Location: Feasterville, PA
Junior Member
Hello Michel,
I want to calculate the average , sum and count(of specific data which will only be either -1 or -2 within data 1 to data155 coulmn)
I am creating a view getting other data as well as inthe query... the sum data , avg and count data will be in new colums in the view.
Can you help
Thanks!
George
Re: HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table [message #237761 is a reply to message #237747] Tue, 15 May 2007 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a small test case with create table and insert statements.
Just a few columns not 150 ones.

Regards
Michel
Re: HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table [message #237767 is a reply to message #237761] Tue, 15 May 2007 11:58 Go to previous messageGo to next message
svgeorge
Messages: 6
Registered: May 2007
Location: Feasterville, PA
Junior Member
This is for generating sum avg and count in crystal reports
below is the sample query i am writing but the select statement is incomplete for sum avg and count as i have no idea how to write.Can you help??
Thanks!
George

CREATE OR REPLACE VIEW v_ifd_tester_data
datetime,
serialnumber,
modelnumber,
assemblyline,
result,
testnumber,
data0,
data1,
data2,
data3,
data4,
data5,
data6,
data7,
data8,
data9,
data10,
"SUM",--(data0 to data10)
"AVG",--(data0 to data10)
"count-1",--(data0 to data10)-this counts the data( which are -1, -2, any number 1 to 100, or 0) from 1 to 10 where data value is -1 else null
"count-2"----(data0 to data10)-this counts the data( which are -1, -2, any number 1 to 100, or 0) from 1 to 10 where data value is -2 else null

AS
select ifd_tester.time_stamp,ifd_Tester.serial_number,ifd_tester.model_number,
ifd_tester.assembly_line,ifd_tester.result,ifd_tester.DATA0,ifd_tester.DATA1,ifd_tester.DATA2,ifd_tester.DATA3,ifd_tester.DATA4,ifd_t ester.DATA5,ifd_tester.DATA6,ifd_tester.DATA7,ifd_tester.DATA8,ifd_tester.DATA9,ifd_tester.DATA10
sum(ifd_tester.DATA0,ifd_tester.DATA1,ifd_tester.DATA2,ifd_tester.DATA3,ifd_tester.DATA4,ifd_tester.DATA5,ifd_tester.DATA6,ifd_tester .DATA7,ifd_tester.DATA8,ifd_tester.DATA9,ifd_tester.DATA10)
avg(ifd_tester.DATA0ifd_tester.DATA1,ifd_tester.DATA2,ifd_tester.DATA3,ifd_tester.DATA4,ifd_tester.DATA5,ifd_tester.DATA6,ifd_tester. DATA7,ifd_tester.DATA8,ifd_tester.DATA9,ifd_tester.DATA10)
count-----(data0 to data10)-this counts the data( which are -1, -2, any number 1 to 100, or 0) from 1 to 10 where data value is -1 else null
count-----(data0 to data10)-this counts the data( which are -1, -2, any number 1 to 100, or 0) from 1 to 10 where data value is -2 else null

from ifd_tester
Re: HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table [message #237768 is a reply to message #237767] Tue, 15 May 2007 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read How to format your posts and apply it.

Where is the problem?
sum is data0 + data1 +...
avg is (data0 + data1 +...)/<nb col>
count-1 is decode(data0,-1,1,0)+...
count-2 is decode(data0,-2,1,0)+...

Regards
Michel


Re: HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table [message #237781 is a reply to message #237726] Tue, 15 May 2007 13:05 Go to previous messageGo to next message
svgeorge
Messages: 6
Registered: May 2007
Location: Feasterville, PA
Junior Member
Thanks! Michel SUM and AVG is ok
but for
count-1 is decode(data0,-1,1,0)+...
OR
count-2 is decode(data0,-2,1,0)+...
the syntax is

DECODE(DATA0,-1,1,0)+(DATA1,-1,1,0)+(DATA2,-1,1,0)+(DATA3,-1,1,0)

or

DECODE(DATA0,-1,1,0)+DECODE(DATA1,-1,1,0)+DECODE(DATA2,-1,1,0)+DECODE(DATA3,-1,1,0)

similarly for count -2 also
Please tell me the right syntax
Thanks! once again
Santosh
Re: HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table [message #237783 is a reply to message #237781] Tue, 15 May 2007 13:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
decode for each column.
Just have a look at SQL Reference.

Regards
Michel
Re: HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table [message #237788 is a reply to message #237783] Tue, 15 May 2007 13:18 Go to previous message
svgeorge
Messages: 6
Registered: May 2007
Location: Feasterville, PA
Junior Member
Thanks! you very much michel
for all your help it seems to be working great
Thanks!! once again
Santosh George
Previous Topic: FUNCTION problem
Next Topic: Pl/sql examples
Goto Forum:
  


Current Time: Fri Dec 02 14:14:55 CST 2016

Total time taken to generate the page: 0.09187 seconds