Hi.
I'm having some problems with formulating a SELECT statement to do the
following:
I have a database with one column of names with certain tags bounded by
square brackets, and a price column as well. The database looks something
like this:
ITEM PRICE
Shirt [Large] [Polyester] 12.99
[Small] Pants [CategoryB] 15.99
Sweater [A GRADE] [Medium] 20.00
Tie [Large] [CategoryB] 5.99
Tags (the stuff between the [ and ]) are not standardised and can contain any
string. What I want to do is find the total price for each tag and return the
whole thing in a report grouped by tag like this:
ITEM TAG: TOTAL PRICE:
[Large] 28.98
[CategoryB] 21.98
[Medium] 20.00
[Small] 15.99
[Polyester] 12.99
I was using this SQL statement:
select
substring(Item,instr(Item,\"[\"),instr(Item,\"]\")-instr(Item,\"[\")+1) as
ItemTag,sum(Price) as TotalPrice from Products group by ItemTag order by
TotalPrice desc
As you can see this is rather byzantine and only groups by the first tag it
encounters. I wondered if it was possible to apply it for as many tags as
there are in the item name (as in the example above).
I hope someone can help, I'm reasonably experienced with MySQL but this one
is out of my league.
Thanks for any assistance you can give.
--
Steve Pick
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
