CODEWITHSUNDEEP
phpmysql
user3623126
user3623126

Reputation: 91

How to count individual records of table with total count

I need to get count of individual records in a table on the basis of type. My "types" are:

delhi
mumbai
banglore
calcutta

.

Delhi consist 8000 records
banglore consist 2000 records
mumbai consist of 31000 record
calcutta consist of 4000 record

I need this individual records with total recors count 

Delhi consist 8000 records
banglore consist 2000 records
mumbai consist of 31000 record
calcutta consist of 4000 record
totoal records  45000

I use this query --

SELECT TYPE ,COUNT(*) AS COUNT FROM `caselaw` GROUP BY TYPE  ORDER BY COUNT DESC;

and I'm getting this only 

Delhi consist 8000 records
banglore consist 2000 records
mumbai consist of 31000 record
calcutta consist of 4000 record

but I also need this 

totoal records  45000

my table structure is 

id type data 
1  Delhi abc
2 mumbai xyz
3 mumbai mno
1  Delhi xyz
2 mumbai abc
3 mumbai bla
1  Delhi bla
2 banglore  etc
3 mumbai  etc

Upvotes: 0

Views: 79

Answers (3)

Aman Aggarwal
Aman Aggarwal

Reputation: 18449

SELECT TYPE, COUNT() AS COUNT FROM table_name GROUP BY TYPE UNION SELECT 'total records' AS TYPE, COUNT() AS COUNT FROM table_name ;

Upvotes: 0

Arnab Bhagabati
Arnab Bhagabati

Reputation: 2715

A simpler way to do this

SELECT TYPE ,COUNT(*) TYPE_COUNT, (select count(*) from caselaw) TOTAL_RECORDS
          FROM caselaw GROUP BY TYPE  ORDER BY TYPE_COUNT DESC;

Upvotes: 0

Cyrano
Cyrano

Reputation: 41

i suggest a UNION query, like this :

SELECT
  TYPE,
  COUNT(*) AS COUNT
FROM `caselaw`
GROUP BY TYPE
ORDER BY COUNT DESC
UNION
SELECT
  'total records' AS TYPE,
  COUNT(*) AS COUNT
FROM `caselaw`;

Upvotes: 4

Related Questions