How to fetch data with total count using mysql single query

Question

I want to write one mysql query to fetch all my table data and with total records count.

For eg. this is my table

ID     Name    typeId
1      test1   1
2      test2   1
3      test3   2

If I am going to fetch data with type id 1 I need a result like this

Name    count 
test1   2
test2   2

How can I write sql for this kind of a result. I dont want subquery. I want to fetch data with out subquery Please help me.

Thanks

Is the count just the number of records in the table, or, of something else? — BugFinder, Oct 03 '12 at 06:55
Your edited example doesn't seem to make any sense. Did you perhaps mean to put `typeId` instead of `Name` in the output? Anyway, why is the count equal to 2 in *both* cases? — Andriy M, Oct 03 '12 at 09:28

Himanshu · Accepted Answer · 2012-10-03T07:37:44.767

3

Looking at your desired result this is a bizarre requirement as you want all count(not for each Name). You can use sub-query for that:

SELECT `Name`, (SELECT COUNT(*) FROM myTable) as `COUNT`
FROM myTable

Or if you want count for each Name try this:

SELECT `Name`, COUNT(*) AS `COUNT` 
FROM myTable 
GROUP BY `Name`;

EDIT:

As you have updated your question your query should be like this:

SELECT `Name`,
(SELECT COUNT(*) FROM myTable WHERE TypeId = 1 GROUP BY TypeId) as `COUNT`
FROM myTable WHERE TypeId = 1;

See this SQLFiddle

edited Oct 03 '12 at 07:37

answered Oct 03 '12 at 07:02

Himanshu

@learner. Updated the answer. But remember, Always be clear before asking a question. Please don't change your requirement as you did in this question. If you do this, all given answers will become wrong for updated question. – Himanshu Oct 03 '12 at 07:40
Hi with out using subquery can u fetch same result data? – DEVOPS Oct 03 '12 at 09:05
@learner Looking at your requirement, I don't think without sub-query it is possible. [See this SQLFiddle](http://sqlfiddle.com/#!2/65953/10) – Himanshu Oct 03 '12 at 09:21

score 2 · Answer 2 · answered Oct 03 '12 at 07:06

2

alternatively is to use CROSS JOIN here

SELECT name, c.totalCount   
FROM Table1, (SELECT COUNT(*) totalCount FROM Table1) c

answered Oct 03 '12 at 07:06

John Woo

Please check my updated question. I dont want subquery. I want to fetch data with out subquery – DEVOPS Oct 03 '12 at 09:24

Vikram Jain · Answer 3 · 2012-10-03T09:53:32.923

1

select name,count(*) as count from tablename where typeid=1 group by name

edited Oct 03 '12 at 09:53

answered Oct 03 '12 at 06:59

Vikram Jain

score 0 · Answer 4 · answered Oct 03 '12 at 06:58

0

SELECT name, count(*) AS count FROM <TABLE NAME> GROUP BY name;

answered Oct 03 '12 at 06:58

Salil

score 0 · Answer 5 · edited Oct 03 '12 at 09:28

0

SELECT t.*, count(\*) AS count FROM myTableName as t GROUP BY t.Name;

edited Oct 03 '12 at 09:28

Julien Poulin

answered Oct 03 '12 at 09:10

darshan.sanandiya