SELECT sum(quantity) as TotalQuantity,
cast(RIGHT( '0' + SUBSTRING_INDEX(location , '.' , 1),2) AS
UNSIGNED) as rack,
cast(RIGHT('0' + SUBSTRING_INDEX(SUBSTRING_INDEX(location , '.' ,
2), '.' , -1),2) AS UNSIGNED) as shelf,
cast(RIGHT( '0' + SUBSTRING_INDEX(location , '.' , -1),2) AS
UNSIGNED) as box
FROM stockquantities
WHERE Quantity > 0
AND Location != 'NULL'
GROUP BY rack,shelf,box
ORDER BY rack,shelf,box
The addition of the cast was required for proper ordering, otherwise they
were ordered as text, i.e.: 1,10,2,20,3,30 etc...
--
Jay
> -----Original Message-----
> From: Michael Traher [mailto:[EMAIL PROTECTED]
> Sent: 07 October 2004 15:04
> To: CF-Talk
> Subject: Re: Query problem
>
> It would have spoilt all your fun if I hadn't made at least
> one mistake :-)
>
>
> On Thu, 7 Oct 2004 12:42:36 +0100, James Smith
> <[EMAIL PROTECTED]> wrote:
> > With the exception of the missing "," after the shelf that works
> > perfectly, thanks.
> >
> > --
> > Jay
> >
> >
> >
> > > -----Original Message-----
> > > From: Michael Traher [mailto:[EMAIL PROTECTED]
> > > Sent: 07 October 2004 12:29
> > > To: CF-Talk
> > > Subject: Re: Query problem
> > >
> > > how about
> > >
> > > SELECT sum(quantity) as TotalQuantity,
> > > RIGHT( '0' + SUBSTRING_INDEX(location , '.' ,
> 1),2) as rack,
> > > RIGHT('0' + SUBSTRING_INDEX(SUBSTRING_INDEX(location
> > > , '.' , 2), '.' , -1),2) as shelf
> > > RIGHT( '0' + SUBSTRING_INDEX(location , '.' ,
> -1),2) as box
> > > FROM stockquantities WHERE Quantity > 0 GROUP BY rack,shelf,box
> > >
> > > untested! I don't even have MYSQL but have a look here
> > >
> > > http://dev.mysql.com/doc/mysql/en/String_functions.html/
> > >
> > > SUBSTRING_INDEX(str,delim,count)
> > >
> > > Returns the substring from string str before count occurrences of
> > > the delimiter delim. If count is positive, everything to
> the left of
> > > the final delimiter (counting from the left) is returned.
> If count
> > > is negative, everything to the right of the final delimiter
> > > (counting from the right) is returned.
> > >
> > > mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
> > > -> 'www.mysql'
> > > mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
> > > -> 'mysql.com'
> > >
> > > HTH
> > > Mike
> > >
> > >
> > > On Thu, 7 Oct 2004 11:22:45 +0100, James Smith
> <[EMAIL PROTECTED]>
> > > wrote:
> > > > I have a fairly simple query that needs some modifications that
> > > > are beyond me.
> > > >
> > > > Currently it reads.
> > > >
> > > > SELECT sum(quantity) AS TotalQuantity, Location FROM
> > > stockquantities
> > > > WHERE Quantity > 0 GROUP BY location
> > > >
> > > > Unfortunately the location field is text in the format
> > > xx.xx.xx where
> > > > x is a number, for example 01.01.01 or 34.12.02 which means
> > > rack 34,
> > > > shelf 12, box 2. The problem is that being text 01.01.01
> > > is different
> > > > to 1.1.1 and I need them to be grouped together in this query.
> > > >
> > > > In CF I could use
> > > > numberformat(listfirst(location,"."),"00") & "." & ........
> > > >
> > > > To create the appropriate formatting but there are no such list
> > > > functions in MySQL. Can anyone out there think of a
> way to do this?
> > > >
> > > > --
> > > > James Smith
> > > > [EMAIL PROTECTED]
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
