Solved

mysql count and group by

Posted on 2014-12-08
4
167 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 143

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 143

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 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