Count equal results from DB, how?

[eluser]asppp[/eluser]
Hello fellow CI's.

I have a DB table that has different clothes sizes for different customers.

Let's say I have a table called T-shirts, and every row in the table have the users ID, and a size, let's say "M" (Medium).

What I want to do is to display all of the sizes in a html table, and then count how many of that exact size there is.

like:

S - 5
M - 13
L - 8
XL - 7
XXL - 4

How do I do that?

Thanks

[eluser]Mauricio de Abreu Antunes[/eluser]
You need to sum your results and group them by size (or size/user id).
Bye! :-)

[eluser]iGeordie[/eluser]
Try something such as the following:

Code:

SELECT size, COUNT(*) AS size_count FROM T-Shirts GROUP BY size

Should be a simple case of outputting a table row per $row returned as so:

Code:

<tr><td>$row['size']</td><td>$row['size_count']</td></tr>

I could go into more depth, let me know if you want help with the full solution but I expect this should get you started.

Let me know how you get on.

[eluser]Mauricio de Abreu Antunes[/eluser]
Pay attention:
If you have a field quantity, you need to use "sum".
Otherwise, use "count", counting line records for you query.

[eluser]iGeordie[/eluser]
Why would a single user have multiple t shirt sizes?

[eluser]Mauricio de Abreu Antunes[/eluser]
Good point!
Why do you have user id in clothes table?
Is that a purchase table?

[eluser]asppp[/eluser]
Well, the users are just choosing which sizes they have, not buying anything. The company I built this for wanted it like that.

So what I want to display is how many of the users that have Medium in shirts, and so on.

Thanks for the replies! Have been working with other stuff and gonna try it now

Edit: Oh, didn't read through the replies quite much. To explain more thorough I will show you the table structure:

ID: Tshirts: Shirts: Hoodies;

1 M M XL
2 S S M
3 M L L
4 XL L XL

Like that. So the table displays which sizes each user have, and I want to count each size that is "equal".

So what I want to display is:

T-shirts
----------
Small: 1
Medium: 2
Large: 0
X-large: 1

I think you understand now

Is that possible?

[eluser]aquary[/eluser]
I guess you'll have to normalized the table to count it easier ? I had done something crazier than that (with....everything measurable, fron neck width until length between bib...). but to be simplified for your version...

Table: Items
ID Item_name
1 Tshirts
2 Shirts
3 Hoodies

Table: user_sizes
1 1 M
1 2 M
1 3 XL
2 1 S
2 2 S
2 3 M
3 1 M
3 2 L
3 3 L
4 1 XL
4 2 L
4 3 XL

Then you can use COUNT+GROUP_BY on user_sizes to get sizes summary easily.

Else, you could run a SUM of CASE..... but from the structure, it seems to be a bit hard to archive.