MySql grouping multile fields

Question

I need to group some data from my DB, say if my data is

url, siteId
abc, 1
abc, 1
abc, 2
abc, 2
abc, 2

I need to count the number of URL's at each site. I tried using GROUP BY url and SELECT *, COUNT(url) as urlCount, however, that grouped all 5 together. Is this possible using mysql alone or would I need some PHP to format that data?

You want to GROUP BY url then it will show only 1 row as abc is the only value of the url column. Please clarify what you want. — Vishal R
– Vishal R, Commented Nov 26, 2013 at 11:46

juergen d · Accepted Answer · 2013-11-26 11:43:20Z

1

Group by siteidand not by url

select siteid, count(distinct url) as urlCount
from your_table
group by siteid

If you use distinct then you will get the count of unique urls.

answered Nov 26, 2013 at 11:43

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

1 Comment

TMH Over a year ago

This worked perfectly, I tried that sql before, but without the distinct, thanks! I'll accept this soon as SO lets me.

Aziz Shaikh · Accepted Answer · 2013-11-26 11:43:02Z

1

This should work:

SELECT siteId, COUNT(url) AS urlCount
FROM tablename
GROUP BY siteId

As you wrote, I need to count the number of URL's at each site, for sql query you would write this as GROUP BY siteId.

answered Nov 26, 2013 at 11:43

Aziz Shaikh

16.6k11 gold badges65 silver badges81 bronze badges

Collectives™ on Stack Overflow

MySql grouping multile fields

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related