Q
Problem solve Get help with specific problems with your technologies, process and projects.

Best method to determine number of patients in a department

Is it advisable to make a single query to get the number of male/female/child patients in a given department (more than one) or to make a single query for each (number of males/females/children) for a given department? I have a single query to get the record by using inline query. The query takes a long time if the number of departments is large.
Try the ROLLUP analytical function. ROLLUP enables an SQL statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. It also only does one-pass through the table, so it's efficient too! 
SELECT

   deptno,

   job,

   count(*),

   sum(sal)
FROM

   emp
GROUP BY

   ROLLUP(deptno,job);





   DEPTNO JOB         COUNT(*)   SUM(SAL)
--------- --------- ---------   ---------
       10 CLERK              1       1300
       10 MANAGER            1       2450
       10 PRESIDENT          1       5000
       10                    3       8750
       20 ANALYST            2       6000
       20 CLERK              2       1900
       20 MANAGER            1       2975
       20                    5      10875

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Meet all of our Oracle Database / Applications experts

View all Oracle Database / Applications questions and answers

Start the conversation

Send me notifications when other members comment.

Register

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close