MySQL

Need help with a query

[TheSeoGuy]

Need help with a query

If we have two columns that hold redundant data, such as an ItemID column and a colorID column:

A = itemID 
B = colorID 
+-------+ 
¦ A ¦ B ¦ 
+---+---+ 
¦ 1 ¦ 1 ¦ 
¦ 1 ¦ 1 ¦ 
¦ 1 ¦ 3 ¦ 
¦ 2 ¦ 1 ¦ 
¦ 2 ¦ 3 ¦ 
¦ 2 ¦ 3 ¦ 
¦ 2 ¦ 5 ¦ 
¦ 3 ¦ 5 ¦ 
+---+---+ 

And I want my query to tell me How many colors exist for each item, what would my query be?
I would like my results to be:
For ItemID 1, there exist 2 entries with colorID 1
For ItemID 1, there exist 1 entry with colorID 3
For ItemID 2, there exist 1 entry with colorID 1
For ItemID 2, there exist 2 entries with colorID 3
For ItemID 2, there exist 1 entry with colorID 5
For ItemID 3, there exist 1 entry with colorID 5

I have tried something to the effect of:
SELECT itemID, colorID, count(colorID) from myTable GROUP BY colorID HAVING itemID = 1;

But, this returns skewed results.

Any help would be greatly appreciated.

[dcrombie]

SELECT DISTINCT(column1), COUNT(column2) FROM table GROUP BY column1;

[TheSeoGuy]

Thanks dcrombie,

but that query seems to give me the following results:
For ItemID 1, there exist 3 entries
For ItemID 2, there exist 4 entries
For ItemID 3, there exist 1 entry

That is close, but I need it filtered one step further.

ItemID 1 may have had 3 entires,
but I need to know that ItemID 1 had 2 entries with colorID = 1 and 1 entry with colorID = 3

[slade7]

Pseudo Code - I think this will work

SELECT DISTINCT(column1)
FROM table
GROUP BY column1
ORDER BY column1;

while(looping through that){
$itemid = column1

SELECT column2,
COUNT(column2)
FROM table
WHERE column1 = '$itemid'
GROUP BY column2
ORDER by column2

while(looping through that){
$color = column2
$count = the count

echo "For $itemid there exist $count entries with colorid $color<br>";

}// inner loop

}// outer loop

[coopster]

TheSeoGuy, you were close on your first attempt. Where you erred was in the

GROUP BY

clause.

SELECT itemID, count(colorID), colorID FROM myTable GROUP BY itemID, colorID ORDER BY itemID, colorID;

Remember, when there is a

GROUP BY

clause, there are certain rules that apply for grouping columns. One of those rules is "The Single-Value Rule" -- every column named in the

SELECT

list must also be a grouping column unless it is an argument for one of the set functions. MySQL extends standard SQL by allowing you to use columns or calculations in a

SELECT

list that don't appear in a

GROUP BY

clause. However, we are warned not to use this feature unless the columns you omit from the

GROUP BY

clause are not unique in the group because you will get unpredictable results.

Resource:
[dev.mysql.com...]

[TheSeoGuy]

slade7, I had considered a version of your idea and even implemented it out to a certain degree, but after considering other factors, not mentioned in my post, that looping/querying structure was going to become extremely complex. I was also pretty sure there was a simple query that would run what I needed.

coopster, that worked perfectly! Thank you for your help and especially for the explanation. That was very helpful.

Thanks again to all!

[slade7]

I use a lot of complex querying-looping schemes - mainly because I'm not coopster. I should have added that disclaimer in my post.

I think SQL is Coopsters native language. When he goes to look for socks he's like "SELECT * FROM sock_drawer WHERE sock_color = 'red' LIMIT 0,2"

[coopster]

slade7, I just about wet my pants I laughed so hard. That's some funny stuff. I have a few complex querying/looping schemes myself -- don't we all?

BTW, I keep my socks in two different drawers:

SELECT 
 sock_drawer_1.sock AS leftfoot, 
 sock_drawer_2.sock AS rightfoot 
 FROM sock_drawer_1, sock_drawer_2 
 WHERE sock_drawer_1.sock = sock_drawer_2.sock 
 AND sock_drawer_1.sock_color = 'red' 
;

But, considering the efficiency you have shown, I am hereby changing my ways ;)

[slade7]

Well, with my Wife's laundry system, and my tendency to leave stuff here and there - the process looks something like this for me...

$result = mysql_query
("
SELECT househould.item_location,
items.item_id,
COUNT(household.item_type)AS num_socks
FROM household, items
WHERE items.description LIKE '%sock%'
AND items.color = 'white'
AND items.stripes = 'no'
AND items.item_type = household.item_type
GROUP BY items.item_type, items.color
HAVING num_socks = '2'
LIMIT 0,1
");

$row = mysql_fetch_array($result);
$where_the_heck_some_socks_are = $row[0];

echo "$where_the_heck_some_socks_are";