Home » SQL & PL/SQL » SQL & PL/SQL » SQL query for count
SQL query for count [message #628078] Thu, 20 November 2014 01:00 Go to next message
asbh123
Messages: 26
Registered: November 2014
Location: mumbai
Junior Member
Hi,

I have table like below,

I need count of records in that table and list of ids in another column with seperated by commas.

If i use count(*) we will get count but that ids how to display in another column.Is it possible?

Empid
1
10
20
30
40
50



Required Output:


Empidcount Listofids
6 1,10,20,20,40,50

Lalit : Fixed typo in topic title

[Updated on: Wed, 22 April 2015 03:12] by Moderator

Report message to a moderator

Re: Sql querry for count [message #628079 is a reply to message #628078] Thu, 20 November 2014 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

LISTAGG

Report message to a moderator

Re: Sql querry for count [message #628083 is a reply to message #628079] Thu, 20 November 2014 01:30 Go to previous messageGo to next message
asbh123
Messages: 26
Registered: November 2014
Location: mumbai
Junior Member
Hi ,

when i try with below code for verification it is showing ora-01489 result of string concatenation is too long.

SELECT LISTAGG(empid, ',')
         WITHIN GROUP (ORDER BY emp_id) Emp_list
         FROM tablename

Report message to a moderator

Re: Sql querry for count [message #628084 is a reply to message #628083] Thu, 20 November 2014 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

When I do it works:
SQL> select deptno, count(*) nbemp,
  2         listagg(empno,',') within group (order by empno) emps
  3  from emp
  4  group by deptno
  5  order by deptno
  6  /
    DEPTNO      NBEMP EMPS
---------- ---------- ------------------------------
        10          3 7782,7839,7934
        20          5 7369,7566,7788,7876,7902
        30          6 7499,7521,7654,7698,7844,7900

3 rows selected.

Report message to a moderator

Re: Sql querry for count [message #628085 is a reply to message #628083] Thu, 20 November 2014 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try using TO_CLOB:
SQL> select deptno, count(*) nbemp,
  2         listagg(to_clob(empno),',') within group (order by empno) emps
  3  from emp
  4  group by deptno
  5  order by deptno
  6  /
    DEPTNO      NBEMP EMPS
---------- ---------- ------------------------------
        10          3 7782,7839,7934
        20          5 7369,7566,7788,7876,7902
        30          6 7499,7521,7654,7698,7844,7900

Report message to a moderator

Re: Sql querry for count [message #628104 is a reply to message #628085] Thu, 20 November 2014 03:37 Go to previous messageGo to next message
asbh123
Messages: 26
Registered: November 2014
Location: mumbai
Junior Member
Hi Michel Cadot,

Thanks for the code,the above two codes are working fine only when we provide groupby in 4th line(deptno).But my problem my table have only one column here so it is not possible to apply groupby empid in 4th line.If i applied its not giving my desired output.bcz im taking counts of all the empids in onecolumn and in another column listofthese 6empids seperated by commas.

Report message to a moderator

Re: Sql querry for count [message #628105 is a reply to message #628104] Thu, 20 November 2014 03:40 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
So remove the group by.

Report message to a moderator

Re: Sql querry for count [message #628106 is a reply to message #628105] Thu, 20 November 2014 03:48 Go to previous messageGo to next message
asbh123
Messages: 26
Registered: November 2014
Location: mumbai
Junior Member
If I remove the group by it will not work.It will give error like ora-00937 not a singlegroup function at empid

code I have used below works well when I use groupby but its not giving my desired o/p mentione in post above.
select empid,count(*)nbemp,
listagg(to_clob(empid),',') within group (order by empid) emps
from tablename
group by empid
order by empid


11.2.0.3.0

Report message to a moderator

Re: Sql querry for count [message #628109 is a reply to message #628106] Thu, 20 November 2014 03:54 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
you need to remove empid from the select part as well.

Report message to a moderator

Re: Sql querry for count [message #628111 is a reply to message #628106] Thu, 20 November 2014 03:57 Go to previous messageGo to next message
asbh123
Messages: 26
Registered: November 2014
Location: mumbai
Junior Member
Thanks Michel Cadot ,
Works like charm now.if I keep empid in between count function like below.
select count(empid)nbemp,
listagg(to_clob(empid),',') within group (order by empid) emps
from tablename
order by empid

[Updated on: Thu, 20 November 2014 03:58]

Report message to a moderator

Re: Sql querry for count [message #628124 is a reply to message #628111] Thu, 20 November 2014 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Thanks Michel Cadot


Don't forget cookiemonster who held your hands to make it work when I was not there.

Report message to a moderator

Re: Sql querry for count [message #628251 is a reply to message #628111] Fri, 21 November 2014 19:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
asbh123 wrote on Thu, 20 November 2014 04:57

Works like charm now.


Are you sure? You should still get "ora-01489 result of string concatenation is too long". Using TO_CLOB does't change the fact LISTAGG returns VARCHAR2 (or RAW) and therefore is limited to 4000 bytes:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select listagg(object_name,',') within group(order by object_name) from dba_objects;
select listagg(object_name,',') within group(order by object_name) from dba_objects
                                                                        *
ERROR at line 1:
ORA-01489: result of string concatenation is too long


SQL> select listagg(to_clob(object_name),',') within group(order by object_name) from dba_objects;
select listagg(to_clob(object_name),',') within group(order by object_name) from dba_objects
                                                                                 *
ERROR at line 1:
ORA-01489: result of string concatenation is too long


SQL>


SY.

Report message to a moderator

Re: Sql querry for count [message #628409 is a reply to message #628251] Mon, 24 November 2014 23:18 Go to previous messageGo to next message
asbh123
Messages: 26
Registered: November 2014
Location: mumbai
Junior Member
Thanks cookiemonster for the code.

Solomon Yakobson,I have only 5records in my table so the above code is working for me.But when i check with another table having lot of records then i'm getting the ORA-01489: result of string concatenation is too long.May be because of concatenation of those values into one column is not possible due to lenght problem.


May be cookiemonster will know the solution for this...

Report message to a moderator

Re: Sql querry for count [message #628411 is a reply to message #628409] Tue, 25 November 2014 00:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can search for T. Kyte's STRAGG, there is a version with CLOB result.

Report message to a moderator

Re: Sql querry for count [message #628838 is a reply to message #628078] Fri, 28 November 2014 23:00 Go to previous messageGo to next message
Rajesh Reddy
Messages: 6
Registered: November 2014
Location: Hyderabad
Junior Member
Hi all,
you can use the below query

select deptno, count(1) nbemp,
wm_concat(empno) emps

from emp
group by deptno
order byu deptno
/
DEPTNO NBEMP EMPS
---------- ---------- ------------------------------
10 3 7782,7839,7934
20 5 7369,7566,7788,7876,7902
30 6 7499,7521,7654,7698,7844,7900

Report message to a moderator

Re: Sql querry for count [message #628839 is a reply to message #628838] Sat, 29 November 2014 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WM_CONCAT is a non documented function which is private to Oracle.
So do NOT use it.
In addition, it is present ONLY if you install Workspace Manager.
Also, are you sure WM_CONCAT returns a CLOB?

In the end, why COUNT(1)? Why do want to count some 1? Why 1? Why not 2 or 3?
The correct way is to count rows, that is COUNT(*).

Forgot to mention: it has already been told to you to format your posts.
So read again How to use [code] tags and make your code easier to read.

[Updated on: Sat, 29 November 2014 00:31]

Report message to a moderator

Re: Sql querry for count [message #628840 is a reply to message #628411] Sat, 29 November 2014 02:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Mon, 24 November 2014 22:07

You can search for T. Kyte's STRAGG, there is a version with CLOB result.



https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2196162600402#29842804516386

Report message to a moderator

Re: Sql querry for count [message #628858 is a reply to message #628409] Sat, 29 November 2014 06:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Then use XMLAGG (or write your own STRAGG function using ODCI interface as Michel suggested). XMLAGG:

SQL> select  listagg(object_name,',') within group(order by object_name) list
  2    from  dba_objects
  3    where owner = 'SH'
  4  /
  from  dba_objects
        *
ERROR at line 2:
ORA-01489: result of string concatenation is too long


SQL> select  rtrim(xmlcast(xmlagg(xmlelement("e",object_name,',') order by object_name) as clob),',') l
  2    from  dba_objects
  3    where owner = 'SH'
  4  /

LIST
--------------------------------------------------------------------------------
CAL_MONTH_SALES_MV,CAL_MONTH_SALES_MV,CHANNELS,CHANNELS_DIM,CHANNELS_PK,COSTS,CO
STS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COST
S,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,COSTS,
COSTS,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX
,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COST
S_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PRO
D_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX
,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COST
S_PROD_BIX,COSTS_PROD_BIX,COSTS_PROD_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIM
E_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX
,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COST

LIST
--------------------------------------------------------------------------------
S_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIM
E_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX
,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COSTS_TIME_BIX,COUN
TRIES,COUNTRIES_PK,CUSTOMERS,CUSTOMERS_DIM,CUSTOMERS_GENDER_BIX,CUSTOMERS_MARITA
L_BIX,CUSTOMERS_PK,CUSTOMERS_YOB_BIX,DIMENSION_EXCEPTIONS,DR$SUP_TEXT_IDX$I,DR$S
UP_TEXT_IDX$K,DR$SUP_TEXT_IDX$N,DR$SUP_TEXT_IDX$R,DR$SUP_TEXT_IDX$X,FWEEK_PSCAT_
SALES_MV,FWEEK_PSCAT_SALES_MV,FW_PSC_S_MV_CHAN_BIX,FW_PSC_S_MV_PROMO_BIX,FW_PSC_
S_MV_SUBCAT_BIX,FW_PSC_S_MV_WD_BIX,PRODUCTS,PRODUCTS_DIM,PRODUCTS_PK,PRODUCTS_PR
OD_CAT_IX,PRODUCTS_PROD_STATUS_BIX,PRODUCTS_PROD_SUBCAT_IX,PROFITS,PROMOTIONS,PR
OMOTIONS_DIM,PROMO_PK,SALES,SALES,SALES,SALES,SALES,SALES,SALES,SALES,SALES,SALE
S,SALES,SALES,SALES,SALES,SALES,SALES,SALES,SALES,SALES,SALES,SALES,SALES,SALES,

LIST
--------------------------------------------------------------------------------
SALES,SALES,SALES,SALES,SALES,SALES,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CH
ANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BI
X,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_
CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_
BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALE
S_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNE
L_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SALES_CHANNEL_BIX,SA
LES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_C
UST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_B
IX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SA
LES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_C

LIST
--------------------------------------------------------------------------------
UST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_CUST_B
IX,SALES_CUST_BIX,SALES_CUST_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SA
LES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_P
ROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_B
IX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SA
LES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_P
ROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROD_BIX,SALES_PROMO_
BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_
BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_
BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_
BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_

LIST
--------------------------------------------------------------------------------
BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_
BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_PROMO_BIX,SALES_TIME_BIX,SALES_TIME_BI
X,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SAL
ES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TI
ME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BI
X,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SAL
ES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TIME_BIX,SALES_TI
ME_BIX,SALES_TRANSACTIONS_EXT,SUPPLEMENTARY_DEMOGRAPHICS,SUP_TEXT_IDX,SYS_IL0000
074366C00006$$,SYS_IL0000074372C00002$$,SYS_IOT_TOP_74370,SYS_IOT_TOP_74375,SYS_
LOB0000074366C00006$$,SYS_LOB0000074372C00002$$,TIMES,TIMES_DIM,TIMES_PK


SQL>


SY.

Report message to a moderator

Re: Sql querry for count [message #636329 is a reply to message #628839] Tue, 21 April 2015 10:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Sat, 29 November 2014 12:00
WM_CONCAT is a non documented function which is private to Oracle.
So do NOT use it.
In addition, it is present ONLY if you install Workspace Manager.


Sorry for answering to such an old question, however I thought it is necessary to point out this:

Any application which has had been relying on wm_concat function will not work once upgraded to 12c. Since, it has been removed from the latest 12c version.

SQL> select banner from v$version where rownum = 1;
    
    BANNER
    ----------------------------------------------------------------------------
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
    
    SQL> SELECT object_name
      2  FROM dba_objects
      3  WHERE owner='WMSYS'
      4  AND object_name LIKE 'WM\_%' ESCAPE '\';
    
    OBJECT_NAME
    ----------------------------------------------------------------------------
    WM_REPLICATION_INFO
    WM_RDIFF
    WM_PERIOD
    WM_PERIOD
    WM_OVERLAPS
    WM_MEETS
    WM_LESSTHAN
    WM_LDIFF
    WM_INTERSECTION
    WM_INSTALLATION
    WM_GREATERTHAN
    WM_EVENTS_INFO
    WM_ERROR
    WM_ERROR
    WM_EQUALS
    WM_DDL_UTIL
    WM_DDL_UTIL
    WM_CONTAINS
    WM_COMPRESS_BATCH_SIZES
    WM_COMPRESSIBLE_TABLES
    
    20 rows selected.
    
    SQL>


You will receive an "invalid identifier" error:

SQL> SELECT banner FROM v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL> SELECT deptno, wm_concat(ename) FROM emp;
SELECT deptno, wm_concat(ename) FROM emp
               *
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier


Therefore, there is no point relying on an undocumented feature which is no more made available in latest versions.

Edit : Added a test case

Regards,
Lalit

[Updated on: Wed, 29 April 2015 03:57]

Report message to a moderator

Re: Sql querry for count [message #636333 is a reply to message #636329] Tue, 21 April 2015 15:53 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Excellent. Thank you. I can now finally tell some of the developers to STOP using it.

Report message to a moderator

Re: Sql querry for count [message #636338 is a reply to message #636333] Wed, 22 April 2015 03:12 Go to previous message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Some people are going to get a nasty shock

Report message to a moderator

Previous Topic: UTL File reader
Next Topic: complex view query with multiple lookups
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 15:54:06 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.