PL/SQL :: Count Columns In A Row

May 3, 2013

oracle SQL,

Student(unique) has five races in a table

I need a select statement with student_id - 1 column

I need to count the number columns with values 'Yes' in them

the columns are race1,race2,race3,race4,race5 for a single row --- this should be 2 ndcolumn

The 3rd column should contain the max of the race having 'yes' in them

i.e if race1 and race 3 have values'yes' in them i need 3 in the next column, - 3rd column

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: Count In Columns?

Feb 14, 2012

I have the following table with insert below.

CREATE TABLE ATTENDANCE
(
"REG_NO" NUMBER(7,0),
"COURSE_SEQ_NO" NUMBER(4,0),
"SECTION" VARCHAR2(1 BYTE),
"SEMESTER_CODE" NUMBER(1,0),

[code]...

How can I have the following output?

course_seq_nosectiontotal_recordscount_1count_0
322X20137

I tried decode but it could not find its way

View 9 Replies View Related

SQL & PL/SQL :: Getting DISTINCT Count From Two Different Columns?

Feb 1, 2012

I have following query which gives currency code from two different tables. I would like to get the distinct count of currency codes from these two different columns.

SELECT eb.person_seq_id, eb.bonus_amount, eb.currency_cd, ed.currency_cd_host
FROM fr_emp_bonuses eb, fr_emp_details ed, fr_periods p
WHERE eb.person_seq_id = ed.person_seq_id AND ed.period_seq_id = eb.period_seq_id
AND ed.period_seq_id = p.period_seq_id AND p.period_status = 'CURRENT'
AND eb.bonus_amount >= 0 AND eb.person_seq_id = 3525125;

This query gives following result

3525125240000USDINR
35251250 USDINR
352512560000 USDINR
352512550000 USDINR

There are two distinct currency codes (USD, INR) and total amount is 350000. So I am looking for a query to give me the following result

3525125350000 2

View 9 Replies View Related

PL/SQL :: Adding Columns To Count Contents Of Other?

Aug 31, 2013

how to incorporate counts into my current SQL code  that would count food items, and then car items for each person. Once I understand this concept I hope to apply it to my real database (in Oracle) where I am counting medications and problems for each patient.For example using my test data below, the my goal would be to have count columns show that Cathy had 3 food count and a 1 cars item (the count would need to ignore the null values in these  columns). I don't really care if the count value repeats in every row for that person.

 Select data1.Name, data1.Category,Data1.Results,Case When data1.Category IN ('food') then data1.results end FOOD,Case When data1.Category IN ('Cars')then data1.Results end CARSFROM(Select T1.name Name, null Category, null Results from a_Main T1, a_Food T2 Where T1.cpi_seq = T2.CPI_SEQUNION Select T1.name, 'food', T2.Food  from a_Main T1, a_Food T2 Where T1.cpi_seq = T2.CPI_SEQUNIONSelect T1.name, 'cars',  T3.Cars  from a_Main T1, a_Cars T3 Where T1.cpi_seq = T3.CPI_SEQ)data1where data1.Category is not nullorder by Name,

[code]....

View 2 Replies View Related

Group By Multiple Columns / Count And Then Find A Max

May 24, 2008

I have three tables,let's say

table stores
sid | store_name
1 | one
2 | two
3 | three

table products
pid | sid | p_name
1 | 2 | pone
2 | 2 | ptwo
3 | 3 | pthree

table sales
said | sid | pid
1 | 2 | 1
2 | 3 | 1
3 | 2 | 2
4 | 1 | 3
5 | 2 | 2
6 | 3 | 2
7 | 3 | 2

and i want display the product that sells best in every store. I try to group by multiple columns counting how many times each product was sold in every store, but don't know how to select the one which was best sold (maximal number of times)

View 5 Replies View Related

Reports & Discoverer :: Create A Report By Using One Field / Text As Columns Name In Layout But Display All Columns

Jun 16, 2010

I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.

View 4 Replies View Related

Performance Tuning :: Index 15 Columns When Cannot Predict Columns Will Be Used In Where Clause?

Apr 4, 2011

I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.

Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.

Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.

So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.

Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.

What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.

I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.

What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.

So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.

View 12 Replies View Related

SQL & PL/SQL :: Difference Between Count(*) And Count(1)?

Nov 16, 2009

When we execute select count(*) from table_name it returns the number of rows.

What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?

View 13 Replies View Related

SQL & PL/SQL :: Difference Between Count(1) And Count(*)

Nov 24, 2011

difference between count(1) and count(*). As i know count(*) will give number of rows irrespective of null and count(1) will not count the null.My Oracle version is 10 g.

SQL> select * from t1;

A B C
---------- -------------------- --------------------
1 2 3
2
5

SQL> select rownum,a.* from t1 a;

ROWNUM A B C
---------- ---------- -------------------- --------------------
1 1 2 3
2 2
3 5
4
[code]....

View 3 Replies View Related

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

Jan 21, 2011

I have a two question.

Question 1:How to select all columns from table except those columns which i type in query

Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data

View 5 Replies View Related

SQL & PL/SQL :: Function In Oracle To Select Not-null Columns At Beginning And Null Columns At End?

Jul 12, 2012

I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :

Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

View 8 Replies View Related

SQL & PL/SQL :: Divide Count From One Query By Count From Another Query

May 24, 2010

I have the folloiwng two queries:

Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;

I need to compute the following:

var:=(yy/zz)*100

How can I achieve this in a single query?

View 3 Replies View Related

SQL & PL/SQL :: Get Count On Group And Total Count For Each Group

Mar 23, 2013

I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )

select
c.Event,
c.code,
count(1) as calls,
total.total_count,
count(1) / total.total_count * 100 as pct_of_total
from
table1 c

[Code]....

[Edit MC: add code tags, do it yourself next time]

View 4 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

PL/SQL :: How To Count

Feb 28, 2013

I have one tbale like

TABLE A with columns bu_code and bu_cty

bu_code bu_cty
12 ES
12 ES
13 AP
13 AP

so now i want to know the count for bu_code

like my output shd be

bu_code(12)    ---count is 2
bu_code(13)------count is 13

View 4 Replies View Related

How To Get COUNT On Query

Mar 4, 2009

I have this query and I want to get the COUNT:

SELECT first_np AS n_p FROM dvc
UNION
SELECT second_np AS n_p FROM dvc
UNION
SELECT n_p FROM dc;

This returns one column which is the n_p; how do I get the count of n_p?

View 2 Replies View Related

Count For A Particular String?

Oct 9, 2008

say there is astring "mumbojumbo "i need the count of given string in it

ex:when o is given count shuld be 2 when m is given count shuld be 3

is there any pre defined function for counting a given string ...

View 2 Replies View Related

SQL & PL/SQL :: Count Age In A Range

Mar 26, 2012

I have a table with the following fields :

RGS_CAN_PERIOD NUMBER(4)
RGS_ID_ELECTOR NUMBER(
FBD_NID VARCHAR2(14)
RGS_SURNAME VARCHAR2(40)
RGS_O_NAME VARCHAR2(40)
RGS_ALIAS VARCHAR2(30)
RGS_ADDR VARCHAR2(75)
RGS_ID_REG_AREA VARCHAR2(3)

[Code]..

I need a report as hereunder :

Reg. Area Age <=19 20 <= Age <= 24 25 <=Age <= 29 Total No. of Voters
xxxx 10 15 7 32
yyyy 5 7 3 15

I have work out a script but the age is not in a range

select *
FROM (select rgs_id_reg_area,
count(decode(fbd_age,19,fbd_age)) Age19,
count(decode(fbd_age,20,fbd_age)) Age20
FROM rubyvoterstat where vote ='Y'
GROUP by rgs_id_reg_area)
order by rgs_id_reg_area

View 1 Replies View Related

SQL & PL/SQL :: Count And Sort?

Mar 10, 2010

I am running this query but am not getting data that is correct.

SELECT a.prod_id, a.prod_name, a.artist_name, COUNT(*)
FROM po_my_purchase_tb a, cm_track_tb b
WHERE a.prod_id = b.prod_id and b.GNR_CD = 'GR000017' AND a.purchase_date > '10-FEB-10' AND ROWNUM<50
GROUP BY a.prod_id, a.prod_name, a.artist_name, a.buy_seq
ORDER BY COUNT(*) desc

View 8 Replies View Related

SQL & PL/SQL :: Group By For Getting Count

Mar 7, 2013

I need suing group by for getting count.I have a table with columns below

SYS_AUDIT_IDSYS_AUDIT_PROG_IDPROG_FINDING_ID_COUNT

178921652
178921641
178921631
179321521
179321511
179321501
179321491
179321461

I want to count number of SYS_AUDIT_PROG_ID for each audit and count of PROG_FINDING_ID_COUNT

I want to get
1789 3 4

I tried this query but this is not working

[code]select sys_audit_id ,count (sys_audit_prog_id), count(prog_finding_id_count) from

my_table sub
group by sys_audit_id [/code]

View 2 Replies View Related

SQL & PL/SQL :: Get Accumulated Value For A Count

Dec 13, 2012

I need to get an accumulated value for a count. E.g. The table has purchased date, purchased item, purchased item type. The count of purchased item groyup by purchased type on every purchased date. Now, we got the count value (purchased item). But, I want the accumulated count value on every purchased date. So that I will get that how many items has been purchased on a particular date.

View 5 Replies View Related

SQL & PL/SQL :: Joins With COUNT

Nov 16, 2011

I pulled in 1121 SSN's into a table and am using that table as the basis for returning data from other tables...including how many documents a user has in their folder.

My query; however, is only returning 655 rows...it is returning only those rows that have documents in their folders. I want to return ALL rows...WHETHER OR NOT THEY HAVE A DOCUMENT COUNT (count(*)). How can I get all 1,121 rows to return? I would like the output to look like:

SSN LOCATION EMP_STATUS FOL_STATUS COUNT(*)
-- For those folders containing documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER 12

-- For those folders containing 0 documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER NULL

Here is the query in it's current state:

-- Get User/Folder/Doc Count Information
SELECT b.ssn,
b.location,
b.emp_status,
c.fol_status,
COUNT (*)

[Code]....

So again, my problem here is that...not all FOLDERS contain DOCUMENTS...but I still want the folder data lised...I just need it listed with either a zero count (0), or a NULL in the COUNT(*) column.

I'm trying the various joins, but none of them seem to be working.

I've tried the old 8i (+) join as follows:

AND c.fol_id = d.doc_fol_id(+)

I've tried the inner join:

AND c.fol_id(+) = d.doc_fol_id

...and I've tried the 9i method (left outer and full outer) using the following types of notations:

folder c full outer join documend d on c.fol_id = d.doc_fol_id

...so far, no luck. I'm still having only 655 rows returned (the 655 are those folders that HAVE document count > 0. Any folder that has zero documents in the document table just aren't being returned in the query.)

View 9 Replies View Related

SQL Inline Count

Jun 14, 2007

we're having a few tables which queries about 10.000 articles. As we don't show them all at once we are using pagination and use the rownum to show only a limited number of the results.

Now as these queries are pretty complex we have to optimize them and since we use pagination we have to call our query twice (first we make a count(*) and then we call the small resultset of a few rows). Ofcourse we are looking for a solution to call it only once and still use the pagination. We could load the whole resultset of 10.000 results and let java show only a few but that makes our line between the oracle and webserver pretty heavy. Is there a way to call the total number of results and give back only a small resultset just in one query?

View 1 Replies View Related

PL/SQL :: How To Do Count In Table

Sep 24, 2012

I have a table employee

name title

A manager
B sales
c manager
D engineer
E marketing

I want to know how many people has title as manager?

the answer is 2 how to do the count, I can't do count(title), right ?

View 2 Replies View Related

SQL & PL/SQL :: Count No Null

Jul 13, 2011

SQL> select * from test11;

A B C
--------------------------------------
AA 123
BB 100 200
CC 300
AA

How to count no null in the above table

View 9 Replies View Related

PL/SQL :: Select All With Count And Sum

Jan 8, 2013

i was just looking to find out how it is possible to select * from a sub select that has data coming from 2 tables.

For example:

select *, count(*) AS Count
from
(
select ....does not work, but i am not sure how i can select all fields from the sub query.

View 3 Replies View Related

SQL & PL/SQL :: Count From 1 Table By 2

Apr 23, 2012

I want the number of UNITS with the Status EN/PL for AREA 3/6

ID 1 has area 3 and 6 but only EN so count 1 (no PL)
ID 2 has area 6 but DR so no count
ID 3 has area 3 and 6 for EN and PL so 1 for each
ID 4 has area 6 three times and 3 once so count.

CREATE TABLE DAN_T1
(
ID varchar(8),
AREA varchar(8),
UNIT varchar(8),
STATUS varchar(8)
)

[Code]...

WANT:

IDENROLLEDPLANNED
11 0
20 0
31 1
41 3

View 2 Replies View Related

SQL & PL/SQL :: Transpose Rows-to-columns And Columns-to-rows

Oct 6, 2010

I need to transpose the following table columns to rows and rows to columns...Im not quite sure how to acheive this...I have the following table with fixed number of columns and dynamic number of rows based on date filter in query

MONTH_YEAR RMS RMS_OCC TTL_RMS
---------------------------------------
SEPTEMBER 200917790017790
OCTOBER 2009183831278818347
NOVEMBER 2009177901460517762

and I need to display this as

COL1 SEPTEMBER 2009 OCTOBER 2009 NOVEMBER 2009
--------------------------------------------------------------
RMS 17790 18383 17790
RMS_OCC 0 12788 14605
TTL_RMS 17790 18347 17762

View 3 Replies View Related

Day Wise Count For A Given Year

Jul 2, 2011

In oracle query can i want find out how many day wise count for a year days (for example how may sundays, mondays, tuesdays, wednesdays ,thursdays,fridays,saturdays) in a given year (we can give the start day of the year and the end day of a year).

example
----------
jan sun-5 mon-4 tue-5 wed-5 thu-5 fri-4 sat-5
feb ------------do---------------------------------
like this for all 12 months at a single query.

View 3 Replies View Related

Find Out Row Count From Each Table?

May 6, 2009

I have written a script to get count of each tables of a schema.

DECLARE CURSOR c1 IS
select distinct table_name from all_tab_columns where OWNER='PLAY';
countrows NUMBER;

[Code].....

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved