Oracle - Group-level summaries

I am trying to create a report that has a summary for each group. For example:

ID           NAME              COUNT           TOTAL     TYPE
-------------------------------------------------------------
1            Test 1            10                         A
2            Test 2            8                          A
                                               18

7            Mr. Test          9                          B
12           XYZ               4                          B
                                               13

25           ABC               3                          C
26           DEF               5                          C
19           GHIJK             1                          C
                                               9

I have a query that can do everything except the TOTAL columns:

       select sd.id DATA_REF_NUM ID, count(sd.DATA_DEF_ID) COUNT, defs.data_name NAME, sd.type
       from some_data sd, data_defs defs
       where sd.data_def_id = defs.data_def_id
       group by some_data.type, some_data.id, defs.data_nam
       order by some_data.id asc, count(amv.MSG_ID) desc ;

I'm just not sure how to get a summary on a group. In this case, I'm trying to get a sum of COUNT for each group of ID.

UPDATE:

Groups are by type. Forgot that in the original post.

TOTAL is SUM(COUNT) for each group.

Answers


How about using ROLLUP like...

select sd.id DATA_REF_NUM ID, count(sd.DATA_DEF_ID) COUNT, defs.data_name NAME, sd.type from some_data sd, data_defs defs where sd.data_def_id = defs.data_def_id group by ROLLUP(some_data.type, (some_data.id, defs.data_nam)) order by some_data.id asc, count(amv.MSG_ID) desc ;

This works for a similar example in my database, but I only did it over two columns, not sure how it will function over more... Hope this is helpful, Craig...

EDIT: In a ROLLUP, columns you want to sum over but not subtotal over like id and data_nam should be lumped together inside the ROLLUP in parantheses)


Assuming SQL*Plus, you could do something like this:

col d1 noprint
col d2 noprint
WITH q AS
(SELECT sd.id, count(sd.DATA_DEF_ID) COUNT, defs.data_name NAME, sd.type
   FROM some_data sd JOIN data_defs defs ON (sd.data_def_id = defs.data_def_id)
  GROUP BY some_data.type, some_data.id, defs.data_nam)
SELECT 1 d1, type d2, id, count, name FROM q
UNION ALL
SELECT 2, type, null, null, null, SUM(count) FROM q GROUP BY 2, type
 ORDER BY 2,1,3;

I can't make this work in PL/SQL Developer 8, only SQL*Plus. Not even the command window will work...


Try a subquery that returns the count of all the items of the type. This would

select sd.id DATA_REF_NUM ID, count(sd.DATA_DEF_ID) COUNT, tot.TOTAL_FOR_TYPE, defs.data_name NAME, sd.type
   from some_data sd, data_defs defs, 
      (select count(sd2.DATA_DEF_ID) TOTAL_FOR_TYPE 
       from some_data sd2
       where sd2.type = sd.type) tot
   where sd.data_def_id = defs.data_def_id
   group by some_data.type, some_data.id, defs.data_nam
   order by some_data.id asc, count(amv.MSG_ID) desc ;

Need Your Help

How can one conduct a code review with GitHub?

github comments code-review

GitHub has a great mechanism for discussion around commits and pull requests. However, if I have created a repo, and I'd like someone to comment on all of the code in my repo, what's a good way to ...

What events and/or methods does the iOS video player expose?

objective-c ios cocoa-touch documentation quicktime

I'm not an iOS developer, but I'm just trying to figure out whether it's possible to query the iOS native video player in a Cocoa Touch app at runtime, and say find out how much of the video has been

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.