GROUP BY with total count, 1 query?

[Glowball]

Hi, how would I combine these two queries into one?

SELECT myfield, COUNT(myfield) "count" 
FROM mytable 
GROUP BY myfield

SELECT COUNT(myfield) 
FROM mytable

I don't want the count of groups, but the count of records involved (for example, 200 records may be 5 groups, I need the "200&quot. Thanks!

 

[fluteplr]

I am not sure if this is ANSI, but in sql server it would be

SELECT myfield, COUNT(myfield) "count"
FROM mytable
GROUP BY myfield with rollup

 

[Glowball]

I'm using Oracle -- talked to our DBA and she said that this won't work (tried it myself, no luck). The only reference I see to "rollup" in Oracle is for something different, I think.

Thanks though!

 

[Glowball]

I think this is what I need:

http://www.dbasupport.com/forums/showthread.php?threadid=15268

It's supported as of 8i but I can't get it to work. Anyone else out there have any luck with ROLLUP?

 

[Morpheus1981]

SELECT m.myfield, c.nCount
FROM mytable M, (SELECT COUNT(myfield) nCount
FROM mytable) c
GROUP BY m.myfield

I think this might work for u

 

[carp]

It might help if you post what you would like the final output to look like. Also, what interface are you using? The answer will vary depending on what you want and if you are using SQL*Plus or Reports or HTML.

Your original post is confusing; you say you want the "count of records involved (for
example, 200 records may be 5 groups, I need the "200&quot". Your second query gives this to you.

If you want a full breakout and then a total, then rollup should work. Also, if you are using SQL*Plus, you can compute the sum on a break.

 

[Glowball]

fdalorzo, I'll try yours tomorrow and let you know how it works, thanks!

carp, sorry if I was confusing, I'll be sending the SQL via ColdFusion -- so it has to be a stand-alone statement. I was trying to get the two queries into one, I'm currently using the two queries and it works fine but it seems processor-heavy. I have a page of reports that builds off of 10 different columns so two queries per column adds up to 20. I was thinking that if I could combine the two that would make things a little faster and not so intense.

I've been working with the ROLLUP but it is erroring out on me, wondering if we have the right version and configuration (I guess it was implemented in 8i). I'll get with the DBA tomorrow and see what's up.

Thanks!

 

[carp]

Here is an example using rollup. The table contains all of the aircraft assigned to the 445th Bomb Group during WWII. I think what you are after is similar to this:

SQL> select acft_type, count(*)
2 from bg445.aircraft
3 group by rollup (acft_type);

ACFT_TYPE COUNT(*)
-------------------- ----------
B-24D 1
B-24H 140
B-24J 115
B-24L 2
B-24M 21
279

This was done with a 8.1.7 database; I think 8.0 may have had cube and rollup capabilities but the syntax was slightly different.

You can get the same results using a UNION operator:
SQL> select acft_type, count(*) from bg445.aircraft group by acft_type
2 union
3 select NULL, count(*) from bg445.aircraft group by NULL;

ACFT_TYPE COUNT(*)
-------------------- ----------
B-24D 1
B-24H 140
B-24J 115
B-24L 2
B-24M 21
279

While this still has you going over the table twice, at least you only make one call to the server, thus lowering your network traffic as well as parsing two statements instead of one.

 

[carp]

As an afterthought, the following query is better than the previous one:

SQL> select acft_type, count(*) from bg445.aircraft group by acft_type
2 union all
3 select 'Total', count(*) from bg445.aircraft group by 'Total';

Not only does UNION ALL allow you to avoid the overhead of sorting incurred by UNION, but it also allows you to select whatever you want the label for your last row to be and keep it on the last row (again, because UNION ALL doesn't trigger a sort).

 

[Glowball]

Okay I'll give your suggestions a try... I'm not sure I understand the 'Total' in single quotes like that, is that some sort of pseudo-column? Sorry if I'm being dense, this has been a challenging issue for me (still is).

 

[carp]

Well, sort of. When you're unioning queries, you have to get the columns of all of your queries to match up - both in number of columns and column type. Since the first column in my first query is a character-based column, the first column in the second query must also be a character-based column; however, since the intent of the second query is to just get one number, I have to supply SOMETHING that's character-based for the first column. My choices are NULL or a character string. Since I have to provide a character string, I chose one that would indicate what that number represents.

 

[Glowball]

Geez I need more time at work to actually GET to this issue! So it turns out we're pre-8i on Oracle so I can't use ROLLUP at all. Pretty slick with the UNION, though, thanks. I'll let you know how it turns out.