• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

SQL data grouping

Hi,
We have a table of items, these items MAY or MAY NOT contain the size as part of the item id e.g. ABC/1, ABC/2, DEF. If the size is part of the item id, it is always written as <item>/<size> where <item> is a variable length and <size> is a single character.
When calculating sales figures, I am able to SUM() on quantity for each itemid, however, I would prefer to group all items of the same range as one (e.g. ABC/1, ABC/2 ABC/3 as a range called ABC, and therefore SUM() all ABC figures into one).
Is this possible as part of the query, will I have to use a stored procedure?
Current query: SELECT ITEMID, SUM(QTY) AS SOLD FROM CUSTINVOICETRANS GROUP BY ITEMID

Open in new window

0
clogau_gold
Asked:
clogau_gold
1 Solution
 
tcs224694Commented:
Hi can u explain a bit clear with some sample records and what u want exactly...
0
 
sateeshcvCommented:
Hi,
try the below query

select case when charindex('/',itemid)>0 then substring(itemid,1,charindex('/',itemid)-1) else
itemid end as ItemId,SUm(Qty) as Sold  from CUSTINVOICETRANS
group  by case when charindex('/',itemid)>0 then substring(itemid,1,charindex('/',itemid)-1) else
itemid end
0
 
clogau_goldAuthor Commented:
Thanks very much, this is great.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now