how to calculate SUBTOTAL/TOTAL on count(*) 4

[kuolung]

hello,

here the data i have in my table with this select query:

select name, date, count(*) from mytable
group by name, date
order by name

name date count(*)
------ --------- ----------
john 01/01 3
john 02/01 1
john 03/01 2
smith 12/01 4
smith 02/01 3

however, I WANT TO have the count as SUBTOTAL OR TOTAL of EACH NAME with the result will look like this:

name count(*)
------ ----------
john 6
smith 7

DO I NEED TO USE SOME KIND OF NESTED QUERY OR SUBQUERY? PLEASE HELP ME, THANK YOU SO MUCH.

 

[carp]

Leave out the date:

select name, count(*) from mytable
group by name;

 

[kuolung]

the result for your query would give me only 2 rows:

name count(*)
------ ----------
john 3
smith 4

thanks for answering but No, that's not what i want, i want to calculate the TOTAL of COUNT(*) for EACH NAME like this:

name totalcnt
------ ----------
john 6
smith 7

any ideas? thank you.

 

[karluk]

Have you actually tried Carp's query? As far as I can see, it would work like a charm. Please try it and let us know if there are any problems.

 

[carp]

I think if you will try the query, it will give you exactly what you asked for.

 

[kuolung]

hello, YES!, i have tried Carp's query, and it gives me 2 rows (which are right) but the counts are wrong, here again:

name count(*)
------ ----------
john 3
smith 4

But the TOTAL count for john should be 6, since he has 3 submitted dates (please read my first post). please help me. Thank you so much..........

 

[carp]

Then there is something else that you are not telling us about:
Here's the test table I set up.

SQL> select * from mytable;

NAME THE_DATE
---------- ---------
john 01-JAN-01
john 01-JAN-01
john 01-JAN-01
john 01-FEB-01
john 01-MAR-01
john 01-MAR-01
smith 01-DEC-01
smith 01-DEC-01
smith 01-DEC-01
smith 01-DEC-01
smith 01-FEB-01
smith 01-FEB-01
smith 01-FEB-01

13 rows selected.

Here is the result of YOUR query:

1 select name, to_char(the_date,'MM/YY') mydate, count(*)
2 from mytable
3* group by name, to_char(the_date,'MM/YY')
SQL> /

NAME MYDAT COUNT(*)
---------- ----- ----------
john 01/01 3
john 02/01 1
john 03/01 2
smith 02/01 3
smith 12/01 4

I believe you'll agree that these are identical results.

Here is the result of MY query:

SQL> select name, count(*)
2 from mytable
3 group by name;

NAME COUNT(*)
---------- ----------
john 6
smith 7

I believe you''l agree that these are the results you were asking for.

This is very common SQL, so the question is, what's REALLY going on here?
What RDBMS are you using?

 

[karluk]

Yes, please forgive our scepticism, Erle. The group by clause doesn't work the way you are describing. If you have six rows for John when you group by name and date, you will also have six rows for John when you group by name alone. There must be something else going on.

 

[kuolung]

hello carp and karluk,

I'm so sorry, it's my fault for not paying attention to your answers. you two are so nice and very helpful. I appreciate your help a lot.

one more question hope you don't mind. how do i have this query make into 3 columns similar to or just like below:

NAME count totcnt
---------- ---------- -------
john 3
john 1
john 2
6
smith 3
smith 4
7

is there a way to do so???? thankssssssssssssssss

 

[karluk]

I suspect that there is no way to get the exact output you want without defining a cursor and writing a program to format the output. However the following query will produce both subtotals and totals for each person.

SELECT A.NAME, A.COUNT ,B.TOTCNT FROM
(SELECT NAME, DATE, COUNT(*) AS COUNT FROM MYTABLE
GROUP BY NAME,DATE) A,
(SELECT NAME,COUNT(*) AS TOTCNT FROM MYTABLE
GROUP BY NAME) B
WHERE A.NAME=B.NAME

The output will look like

NAME COUNT TOTCNT
---------- --------- ---------
JOHN 3 6
JOHN 1 6
JOHN 2 6
SMITH 3 7
SMITH 4 7

 

[carp]

It depends. What RDBMS and querying tool are you using?

 

[carp]

If you are using Oracle and SQL*Plus, you can BREAK on name and COMPUTE the totals you want. But that is SQL*Plus at work, not SQL.

 

[karluk]

Thank you, Carp. You have just increased my (rather scanty) knowledge of SQL*Plus commands.

 

[carp]

My pleasure - that's why we have these great forums!!

 

[kuolung]

carp/karkuk
thanks so much again for your help. this forum is so great with all the wonderful people who know a lot and very helpful.....

 

[pdelpy]

Sorry for the late reply... out of office, but the following might work:

SELECT A.NAME as name,
a.date as date,
count(*) as temp,
'' as totcount
FROM MYTABLE a
GROUP BY NAME,DATE

union all

SELECT A.NAME as name,
'' as date,
'' as temp,
count(*) as totcount
FROM MYTABLE a
GROUP BY NAME

order by name, date

Since the Order by is done after cursor return the union set would be ordered. The one problem is that nulls may tend to order at the top of the names so that the total is above the breakdown.

I hope that this is not too late to help
Peter

 

[adam0101]

select name, date, sum(cnt) from
(select name, date, count(*) As cnt from mytable
group by name, date) as tbl
group by name
order by name

Adam

http://www.adventex.com