Solved

mysql count and group by

Posted on 2014-12-08
4
163 Views
Last Modified: 2014-12-12
I have a table as follows:

id       name
1        Complete
2        Incomplete
2        Incomplete
1        packaged
3        complete
4        packaged
4        packaged

How can I do a count statement to get the following output:
complete  2
incomplete 1
packaged 2

The reason that "incomplete" is 1 is because it is grouped by id, the same is with "packaged".  Can someone let me know how I can get a count of "name"  by grouping by 'id" ?

Thank you,
A
0
Comment
Question by:aej1973
  • 2
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40488263
you have to "distinct" first:
select name , count(*)
  from ( select id, name from yourtable group by id, name )
group by name

Open in new window

0
 

Author Comment

by:aej1973
ID: 40490115
I am getting this error "Every derived table must have its own alias". What does this mean?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40490612
it means the subquery needs an alias:
select name , count(*)
  from ( select id, name from yourtable group by id, name ) SQ
group by name 

Open in new window

SQ is the alias name given to the subquery.
side note about "aliases" for columns and tables in general:
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html
0
 

Author Comment

by:aej1973
ID: 40496670
Thank you.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question