Home » SQL & PL/SQL » SQL & PL/SQL » Get count on group and pct of total count for each group
Get count on group and pct of total count for each group [message #580380] Sat, 23 March 2013 11:13 Go to next message
urbanmojo
Messages: 9
Registered: March 2013
Junior Member
Hi:

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...Smile )

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
cross join
		(
		select count(1) as total_count from table1
                where 
		date >= '20-MAR-2013'
		and
		code = 'Code1'
		) total
where
	        c.date >= '20-MAR-2013'
		and
		c.code = 'Code1'
group by
		c.code, 
		c.event,
                total.total_count
order by
		c.code,
		c.event



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

[Updated on: Sat, 23 March 2013 14:07] by Moderator

Report message to a moderator

Re: Get count on group and pct of total count for each group [message #580381 is a reply to message #580380] Sat, 23 March 2013 12:34 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
Hello,


As you didn't provide the DDL/DML for your table, I didn't test the output of the following query, but just looking at your code, it seems to me that the use of analytic functions here is more appropriate.

SELECT  Event, 
        code, 
        COUNT(*) OVER 
            (
                PARTITION BY code, event 
                ORDER BY code, event
            ) AS calls, 
        COUNT(*) OVER () AS total_count,
        COUNT(*) OVER 
            (
                PARTITION BY code, event 
                ORDER BY code, event
            ) / COUNT(*) OVER () * 100 AS pct_of_total       
FROM table1
WHERE date >= '20-MAR-2013' AND code = 'Code1';



So, you may want to take a look at oracle online doc: Analytic functions and in particular the COUNT(*) Analytic function(not to confuse with its aggregate version).

Note: Date is the name of a datatype in oracle, so personally I would not chose it as a name of a column

Regards,
Dariyoosh

[Updated on: Sat, 23 March 2013 12:38]

Report message to a moderator

Re: Get count on group and pct of total count for each group [message #580383 is a reply to message #580380] Sat, 23 March 2013 14:07 Go to previous messageGo to next message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Get count on group and pct of total count for each group [message #580384 is a reply to message #580380] Sat, 23 March 2013 14:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many errors in your query.

"count(1)", no, use count(*) you want to count the rows not some mysterious "1" (why not 2 or 'zulu'?).

"date >= '20-MAR-2013'"

DATE is a reserved word NEVER use it as a name. You can check the reserved words in the v$reserved_words view.
'20-MAR-2013' is a string and NOT a date, the proof:
SQL> select to_date('20-MAR-2013') from dual;
select to_date('20-MAR-2013') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Always use TO_DATE with a format when you use a date constant.

Regards
Michel
Re: Get count on group and pct of total count for each group [message #580385 is a reply to message #580381] Sat, 23 March 2013 14:13 Go to previous message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Note: Date is the name of a datatype in oracle, so personally I would not chose it as a name of a column


and you can't unless you force it with double quotes:
SQL> create table t (date varchar2(10));
create table t (date varchar2(10))
                *
ERROR at line 1:
ORA-00904: : invalid identifier

Regards
Michel
Previous Topic: Complex select query with criteria
Next Topic: column with primary key is shown as nullable=Y in user_tab_columns after online redefinition
Goto Forum:
  


Current Time: Wed Jul 30 05:36:46 CDT 2014

Total time taken to generate the page: 0.29436 seconds