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


ADVERTISEMENT

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

Displaying Only Max Value Of The Count In Query

Jun 18, 2007

I' m doing a query on multiple tables willing to get only top scorers from a certain round. Here's the relevant part of relation:

SOCCER_TEAM(TEAMID, NAME, CITY)
PLAYER (PLAYERID, NAME_SURNAME, DOB, TEAMID)
GAME_STATS(ROUNDID, GAMEID, TIME, PLAYERID, STATTYPE)

TIME is No between 1-90 representing the minute of the game
STATTYPE is IN('GOAL', 'OWN GOAL', 'RED', 'YELLOW')

Here's my sql code for the query:

SELECT ROUNDID, NAME_SURNAME, NAME, COUNT(STATTYPE)
FROM GAME_STATS, PLAYER, SOCCER_TEAM
WHERE PLAYER.PLAYERID IN (SELECT GAME_STATS.PLAYERID FROM GAME_STATS WHERE STATTYPE='GOAL' AND PLAYER.TEAMID = SOCCER_TEAM.TEAMID)
AND STATTYPE='GOAL'
AND GAME_STATS.PLAYERID = PLAYER.PLAYERID
GROUP BY ROUNDID, NAME_SURNAME, NAME
ORDER BY ROUNDID, COUNT(STATTYPE) DESC

This results in correctly displaying all scorers from all the rounds, yet I haven't been able to construct the HAVING clause to display ONLY the top scorers from each round (there can be multiple of them scoring equal top amount of goals and I need to show them all)

p.s. I have underlined primary keys, while foreign keys are in cursive, if it is of any relevance

View 5 Replies View Related

SQL & PL/SQL :: How To Find The Count In The Query

Apr 16, 2010

from the below string i want to find the count of the "<-" using a single query.

string is "aaaa<-bbbb<-ccccc<-ddddd<-eeeeee<-ffffff<-"

If not the query, pointer on using SQL functions.

View 3 Replies View Related

SQL & PL/SQL :: Total Count Per Day Query?

May 31, 2013

I need to modify my query so that it can give me a total(duration) and total(stlmntcharge) per day in april 2013 starting from the 1st till the 30th. At the moment my query looks like below:

SELECT sum(duration),sum(stlmntcharge)
FROM voipcdr
WHERE (calldate >= TO_DATE('20130401','YYYYMMDD') AND calldate <= TO_DATE('20130430','YYMMDD'))
AND (remtrunkid IN (SELECT UNIQUE trunkid FROM trunks WHERE description LIKE '%Telkom%' AND gw_range_id = '61' AND trunkid like '9%'))

or remip in(SELECT UNIQUE startip FROM gateways WHERE rangename LIKE 'vo-za%' OR rangename LIKE 'PC-IS-VOIS%')

AND direction = 'I'

ORDER BY calldate, calltime;

View 6 Replies View Related

SQL & PL/SQL :: Query For Grouping And Count?

Feb 24, 2010

TASK_ID, TASK_STATUS, TASK_OWNER
================================
00001 , OPEN , ABC
00002 , OPEN , XYZ
00003 , WIP , ABC
00004 , CLOSED , XYZ
00005 , WIP , XYZ
00006 , CLOSED , XYZ
00007 , OPEN , XYZ

Output Required
Owner , Open , WIP, Closed
ABC 1 1 0
XYZ 2 1 2

View 9 Replies View Related

SQL & PL/SQL :: Total Of Count Query

Apr 29, 2010

i need the total of the count query I am executing.My query is

SELECT COUNT(*) FROM po_headers WHERE
CREATION_DATE BETWEEN '01-MAR-2010' and '31-MAR-2010' GROUP BY VENDOR_ID

this gives output as
6
1
3
4

My objective is to find the total of distinct vendors for a given date range.how should I modify this query.

View 1 Replies View Related

Count And Group Query?

Apr 23, 2012

I have a report I created and I need to get a Total Count by Plan Code Description for Each State. I do this and get all my data:

SELECT
mv.R1_State,
mv.subscriber_id,
mv.plan_code,
pm.description,
mv.line_of_business,

[code]...

But I cannot get the count I have to do a separate Query to get the count here it is How can I put the two together to get my count information and Report information together in one Report???

Select
Count(pm.description),
mv.R1_State
FROM windsoradm.member_mv mv

[code]....

View 1 Replies View Related

How To Count Siblings In A Hierarchical Query

Sep 24, 2012

How to count siblings in a hierarchical query? I'm trying to get a listing of employees

SELECT LEVEL, last_name||', '||first_name AS Manager, count(employee_id)
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
GROUP BY level

This returns 4 levels. I'm wanting to add up the number of siblings under the level 2 instead of listing them all.

View 1 Replies View Related

SQL & PL/SQL :: Query To Display The Office And Count(*)?

Nov 1, 2012

I need a query to display the office and count(*)

where rownum > 6 i.e

here is the data

office
------
hq
hq
hq
hq
hq
hq1
hq1

[code]....

I need a query to display the top 3 counts and the 4 row should be sum of count of other offices.

my result should look like this

office count(*)
--------- --------
hq 5
hq1 4
hq2 3
other 10

View 4 Replies View Related

SQL & PL/SQL :: How To Get Count On Executing Dynamic Query

Sep 16, 2010

l_query := 'SELECT sedol ' ||
'FROM integration.tmp_attributed_sedol ' ||
'WHERE ' || p_field || '=''' || p_ref_number || ''' ';

by using the execute immediate or any other command, how can i check whether the query returned any rows or not?

View 2 Replies View Related

SQL & PL/SQL :: Delete After Count Oldest Row Query?

Mar 22, 2011

I have one table that have many records. For the maintenance purpose I like to delete old record based on Customer No.-That is Mobile NO.If each Customer have more than 300 records, I like to delete by everyday batch process.can't figure out how to apply each Customer No.(Specific Column), I could sort (order by few column - SAVE_DT or SMS_ARV_CLC) how to write this kind of query? I try rownum but no more progress.

Here is my table
CREATE TABLE TM_060_SMS_TEST
(
SMS_SEQ VARCHAR2(18 BYTE),
SMS_RCV_CLC VARCHAR2(14 BYTE),
CUST_NUM VARCHAR2(12 BYTE),

[code]...

View 12 Replies View Related

SQL & PL/SQL :: Calculate Count Of Range By A Query?

Jun 30, 2010

I have a table which contains two columns containing the range of some products. These range are alphanumeric and i want to calculate the count of this range by a query. .

test case:
CREATE TABLE MARRIAGE_FILE_NAME
(
START_SERIAL_NO VARCHAR2(10 BYTE) NOT NULL,
END_SERIAL_NO VARCHAR2(10 BYTE) NOT NULL,
CATCODE VARCHAR2(10 BYTE) NOT NULL,

[code]....

View 2 Replies View Related

Inconsistent Count Return By Same Query?

Nov 19, 2012

I am Having below query which is having total 664 records and for WHERE Clause (accountno ='13987135') it is having 3 records but when i am taking count it is returning 3 at first time and again returning 4 every time from then onwords.

SELECT Count(*) cnt FROM(SELECT rownum rnum,
secno,
positionname,
tradingsymbol,
cusipcum,
businessclientname,
businessclientid,

[code].....

View 4 Replies View Related

SQL & PL/SQL :: Delete After Count Oldest Row Query BULK

Mar 24, 2011

I like to increase speed to delete our table. Is it possible to use BULK COLLECT or FORALL this query? This is not single delete or select, maybe I got the error? Is it possible to use BULK method to this query?

delete from
TM_060_SFS_TEST
WHERE
rowid in (
SELECT
[code]......

View 1 Replies View Related

SQL & PL/SQL :: Query To Find Count Of Delimiter In Column Val?

Jun 29, 2010

Need a query to find the count of presence of delimiter "," in column VAL for below mentioned table.

Table:

CREATE TABLE N_B_S_1
(
NUM NUMBER,
VAL VARCHAR2(20)
)

Data:

INSERT INTO N_B_S_1 VALUES(1,'A,B,C');
INSERT INTO N_B_S_1 VALUES(2,'D');
INSERT INTO N_B_S_1 VALUES(3,'ER,NF,G,H,XK');
INSERT INTO N_B_S_1 VALUES(4,'LQW,MBV');
INSERT INTO N_B_S_1 VALUES(5,'KS,YJ,WE,RQ,PM');
COMMIT;

Required output:

num, count of delimiters
===== ===========
1 2
2 1
3 4
4 2
5 4

View 5 Replies View Related

SQL & PL/SQL :: Query To Add Total Number Of Count Of Each Table

Jun 13, 2011

I want to know that whether we can write a query to add total number of count of each table.

for example:

select count(*) from emp;

count(*)
14

select count(*) from emp_1;

count(*)
15

select count(*) from emp_2;

count(*)
16

My question is that is it possible to add this all the three results and get the value as 45.

View 3 Replies View Related

PL/SQL :: How To Use CASE And COUNT Statements In SELECT QUERY

Oct 13, 2012

I want to count the batch records using BATCH_ID with CASE statement ,for that i am using below query but its not working ,

SELECT COUNT(*) FROM <TABLENAME> WHERE VNBATCH_ID=CASE WHEN #SDC <10 AND #PERIOD >=10 THEN
0||#SDC||#PERIOD||#BATCH_ID
WHEN #SDC <10 AND #PERIOD <10 THEN
0||#SDC||0||#PERIOD||#BATCH_ID
WHEN #SDC >=10 AND #PERIOD <10 THEN
#SDC||0||#PERIOD||#BATCH_ID
ELSE
#SDC||#PERIOD||#BATCH_ID
END

View 11 Replies View Related

SQL & PL/SQL :: Record Count Mismatch In Dataset And Query Executed

Oct 14, 2011

I am using an query to fetch the data from oracle DB and fill dataset using oledb dataadapter in ASP.net.When i run the same query in PL/SQL i am getting 14952 records,but when i am filling it to dataset i am getting only 13700 records.

View 2 Replies View Related

SQL & PL/SQL :: How To Bypass Putting Select Inside Count Function In Query

Oct 21, 2012

I have 2 tables, ASSIGNMENT and RESEARCH_PAPER. For each research paper, I need to find out :

1. The number of assignments created from it (after a given constant assign date)

2. The number of assignments created from it that have been approved.

3. The number of unique users who have either created or approved an assignment from it

Test data :

create table research_paper (id int, name varchar2(100));
create table assignment (id int, r_paper_id int, assigner_id int, assignee_id int,
approver_id int, assign_date timestamp, approved_yn varchar2(10));
insert into research_paper values (1, 'A');
insert into research_paper values (2, 'B');

[code]....

Assignment :

id r_paper_id assigner_id assignee_id approver_id assign_date approved_yn
-----------------------------------------------------------------------------------------------------------
11 100 200 100 23-10-12 12:00:00.000000000 AMY
22 200 100 200 22-10-12 12:00:00.000000000 AMN
32 100 200 101 24-10-12 12:00:00.000000000 AMY

[code]....

Research_paper:

id name
----------
1A
2B

Expected result :

r_paper_id created approved unique_users
-----------------------------------------------
1 3 2 4
2 3 2 3

I wrote the following query for that :

SELECT rp.id r_paper_id,
COUNT(*) created,
COUNT(
CASE
WHEN a.approved_yn = 'Y'

[code]....

But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.

View 7 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 :: 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 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

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 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 :: 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







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