MYSQL query, trim last character and group by similar items.

[eternal_noob]

Current Query:

$sql=
"SELECT SysproCompanyJ.dbo.InvWarehouse.StockCode, SUM(QtyOnHand - QtyAllocated) AS 'Value'

FROM SysproCompanyJ.dbo.InvWarehouse 

WHERE SysproCompanyJ.dbo.InvWarehouse.QtyOnHand > '0' 
And Warehouse = 'SW'

GROUP BY StockCode

HAVING SUM(QtyOnHand - QtyAllocated) > 0";

Example of current output (If I were to exclude the Having Sum condition):

+--------------+-------+
|  Stock Code  | Value |
+--------------+-------+
|  C-100-01A   |  100  |
|  C-100-01B   |  -90  |
|  C-200-00A   |  145  |
|  C-300-02C   |  -14  |
|  C-300-02D   |   17  |
+--------------+-------+

What I need (cut off the last character of the stock code and sum like stock codes) :

+-------------+-------+
| Stock Code  | Value |
+-------------+-------+
|  C-100-01   |   10  |
|  C-200-00   |  145  |
|  C-300-02   |    3  |
+-------------+-------+

[jazzman1]

Without update the column?

[Zane]

SELECT substring(invW.StockCode, 1, (len(invW.StockCode) - 1)) as sStockCode , SUM(invW.QtyOnHand - invW.QtyAllocated) AS 'Value'
FROM SysproCompanyJ.dbo.InvWarehouse as invW
WHERE invW.QtyOnHand > '0'
And Warehouse = 'SW'
GROUP BY substring(invW.StockCode, 1, (len(invW.StockCode) - 1)) 
HAVING SUM(invW.QtyOnHand - invW.QtyAllocated) > 0

Untested.

[Psycho]

You should update your database to have a column without the letter. I would think you would want one column for the "code" and a separate one for the letter.

 

Also, your query seems to have some inefficiencies. I don't see why you need the WHERE clause and the HAVING clause. Plus, the HAVING clause is using a separate calculation rather than the one that is included in the SELECT field list.

 

Using Zane's example, I would think the query would look like this

SELECT substring(invW.StockCode, 1, (len(invW.StockCode) - 1)) as sStockCode,
       SUM(invW.QtyOnHand - invW.QtyAllocated) AS 'Value'
FROM SysproCompanyJ.dbo.InvWarehouse as invW
WHERE Warehouse = 'SW'
GROUP BY substring(invW.StockCode, 1, (len(invW.StockCode) - 1))
HAVING Value > 0

But again, I think it would be best to update the table structure.

Edited February 24, 2014 by Psycho

[jazzman1]

For gurus!

 

MySQL follows the SQL standart using CHARACTER_LENGTH() and its aliases like CHAR_LENGTH() and LENGTH(). LEN() isn't a part of MySQL string functions

 

PS: No need to substring same result twice b/s is already done in the select

SELECT substring(invW.StockCode, 1, (len(invW.StockCode) - 1)) as sStockCode,
       SUM(invW.QtyOnHand - invW.QtyAllocated) AS 'Value'
FROM SysproCompanyJ.dbo.InvWarehouse as invW
WHERE Warehouse = 'SW'
GROUP BY substring(invW.StockCode, 1, (len(invW.StockCode) - 1))
HAVING Value > 0

// to 

SELECT SUBSTRING(invW.StockCode, 1, (LENGTH(invW.StockCode) - 1)) as sStockCode,
       SUM(invW.QtyOnHand - invW.QtyAllocated) AS 'Value'
FROM SysproCompanyJ.dbo.InvWarehouse as invW
WHERE Warehouse = 'SW'
GROUP BY sStockCode
HAVING Value > 0

Edited February 24, 2014 by jazzman1

[jazzman1]

Also, I'm not realy sure is it possible to use more than one period(.) as separate table/column identifiers like in the example:

FROM SysproCompanyJ.dbo.InvWarehouse as invW

Edited February 24, 2014 by jazzman1

[Barand]

SysproCompanyJ.dbo.InvWarehouse

 

looks more like a MSSQL tablename.

[Zane]

PS: No need to substring same result twice b/s is already done in the select

 

Although I didn't test the query at all, I've came across headaches in the past where I would get an invalid field error. or something something is ambigous.

 

I'm not realy sure is it possible to use more than one period(.) as separate table/column identifiers

 

We always had to use two dots in my DB Admin class...

databaseName.tableName.fieldName

[jazzman1]

According the docs, a FROM clause is an optional in MySQL and ONLY a table reference, no database, nor column. In his example above I see a column reference.