how to group by and count using MySQL

Question

I have data which looks like this:

ID  post_author post_title  guid
3309    21  Should somebody not yet on SQL 2008 wait for SQL 2008 R2, since it's near release?  http://sql.stackexchange.com/questions/379/should-somebody-not-yet-on-sql-2008-wait-for-sql-2008-r2-since-its-near-release
1695    429 How do we politely decline well meaning advice from the Grandmother?    http://moms4mom.stackexchange.com/questions/1208/how-do-we-politely-decline-well-meaning-advice-from-the-grandmother
556 173 Books on how to be a great dad  http://moms4mom.stackexchange.com/questions/1042/books-on-how-to-be-a-great-dad
160 30  Building an ice hockey net cam  http://photo.stackexchange.com/questions/8/building-an-ice-hockey-net-cam
159 30  Generic commercial photo release form   http://photo.stackexchange.com/questions/4/generic-commercial-photo-release-form

I need to create a query that groups the data on part of the GUID field (the root URL) and counts the POST_AUTHOR for each.

The result I am looking for would be like this:

Site    Count of Authors
http://sql.stackexchange.com    1
http://moms4mom.stackexchange.com   2
http://photo.stackexchange.com  2

I would be grateful if someone help me construct the sql.

Virat Kadaru · Accepted Answer · 2009-10-19 16:54:03Z

1

SELECT COUNT(POST_AUTHOR) AS AUTHOR_COUNT, GUID FROM TABLE_NAME GROUP BY GUID

answered Oct 19, 2009 at 16:54

Virat Kadaru

2,2564 gold badges24 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Lyon Over a year ago

Thanks Virat but I only want to group on part of the GUID field, the root url, up to .com

Zepplock · Accepted Answer · 2009-10-19 16:55:12Z

1

It may be possible to construct such a query but will be not optimized.

You should add a column to your table which will have an ID of the site. Then add a new table which will have a preparsed data for the site: domain, path, resource, whether http or https, etc

This way you can be more flexible in searches and will be much faster, since I assume you have few inserts and large number of reads.

answered Oct 19, 2009 at 16:55

Zepplock

29.2k4 gold badges37 silver badges50 bronze badges

Comments

dar7yl · Accepted Answer · 2009-10-19 17:36:23Z

1

Write a SQL FUNCTION - call it for instance, guid_extract(guid), which extracts the pertinent info, then you can add it to a column in your select::

SELECT stuff, otherstuff, guid_extract(guid) as site
  ...
  GROUP BY site;

answered Oct 19, 2009 at 17:36

dar7yl

3,78528 silver badges20 bronze badges

Comments

Vinko Vrsalovic · Accepted Answer · 2009-10-19 17:00:24Z

0

The problem is how to extract the root part of the URL. If we can be sure that every URL will have at least 3 slashes, this will work, using substring_index

select substring_index(guid,'/',3) as site, count(id) as authors from table
group by substring_index(guid,'/',3)

Of course, if you add an extra column with the site only at insert time, everything will be faster, cleaner and safer (you won't have to complexify the query to handle guids with only two slashes)

answered Oct 19, 2009 at 17:00

Vinko Vrsalovic

342k55 gold badges341 silver badges374 bronze badges

Collectives™ on Stack Overflow

how to group by and count using MySQL

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related