sql query count the duplicates

Question

I am having a result as:

Code    Declaration 
123     a1 - 2nos
123     a2 - 230nos
123     a3 - 5nos
123     a1 - 100nos
123     a3 - 6nos

Is it possible to sum the duplicates and i need the output to be displayed like this:

Code    Declaration
123     a1 - 102nos
123     a2 - 230nos
123     a3 - 11nos

score 4 · Accepted Answer · edited May 23 '17 at 12:04

4

I am basing this answer off the previous question that you posted. Here is a CTE version that will split the data and them total the declaration:

;with cte (code, DeclarationItem, Declaration) as
(
  select Code,
    cast(left(Declaration, charindex(',',Declaration+',')-1) as varchar(50)) DeclarationItem,
         stuff(Declaration, 1, charindex(',',Declaration+','), '') Declaration
  from yourtable
  union all
  select code,
    cast(left(Declaration, charindex(',',Declaration+',')-1) as varchar(50)) DeclarationItem,
    stuff(Declaration, 1, charindex(',',Declaration+','), '') Declaration
  from cte
  where Declaration > ''
),
s2 as
(
  select code, 
    ltrim(rtrim(left(declarationitem, charindex('-', declarationitem)-1))) col1
    , reverse(left(reverse(declarationitem), charindex('-', reverse(declarationitem))-1)) col2
  from cte
),
fnl as
(
  select code, col1,
    left(col2, patindex('%[Aa-Zz]%', col2)-1) value,
    substring(col2, patindex('%[Aa-Zz]%', col2), len(col2)) str
  from s2
) 
select code, col1 +' - '+ cast(sum(cast(value as int)) as varchar(50)) + str as declarationitem
from fnl
group by code, col1, str

See SQL Fiddle with Demo

The result is:

| CODE | DECLARATIONITEM |
--------------------------
|  123 | 123 a1 - 102nos |
|  123 | 123 a2 - 230nos |
|  123 |  123 a3 - 11nos |

edited May 23 '17 at 12:04

Community

1
1

answered Nov 24 '12 at 13:12

Taryn

242,637
56
362
405

is it possible to split this hyphen in declaration item too...it means i need to display as a1 - 102 nos to three columns..if the hyphen will be in a column then i removed it and display that in a web page – Affan Dec 04 '12 at 05:46
@Affan what do you wan the result to look like? – Taryn Dec 04 '12 at 10:09
i need to print this sql result in a asp web page..i need to split this declaration item into two..i dont want to show this hyphen.. – Affan Dec 04 '12 at 11:50
i need the result to be displayed in a web page as Code Declaration Qty 123 a1 102 nos 123 a2 230 nos 123 a3 11 nos i need that declaration item to be splited in to two without hyphen...Can you guide me – Affan Dec 04 '12 at 12:00
For future reference when you have another question, you should just post a new one. – Taryn Dec 04 '12 at 12:12

score 2 · Answer 2 · answered Nov 25 '12 at 02:58

Declare @t Table([Code] int, [Declaration] varchar(max))    
Insert Into @t VALUES (123,'a1 - 2nos'),(123,'a2 - 230nos'),(123,'a3 - 5nos'),(123,'a1 - 100nos'),(123,'a3 - 6nos')


;With Cte As(
Select 
    Code
    ,Substring([Declaration],0,PatIndex('%-%',[Declaration])) Part1 
    ,Cast(Substring(LTRIM(RTRIM(Substring([Declaration],PatIndex('%-%',[Declaration])+1,Len([Declaration])))),0,PatIndex('%nos%',LTRIM(RTRIM(Substring([Declaration],PatIndex('%-%',[Declaration])+1,Len([Declaration])))))) As Int) Part2
From @t)
Select Code,Part1 + '-' +  Cast(Sum(Part2) AS Varchar(10)) + 'nos' As Declaration
From Cte
Group By Code,Part1

score 1 · Answer 3 · answered Nov 25 '12 at 12:36

SELECT Code, SUBSTRING(Declaration, 0, CHARINDEX('-', Declaration) + 2) + 
             CAST(SUM(SUBSTRING(Declaration,
                                CHARINDEX('-', Declaration) + 2, 
                                PATINDEX('%[Aa-Zz]%', SUBSTRING(Declaration, 
                                                                CHARINDEX('-', Declaration) + 2,
                                                                LEN(Declaration)
                                                                )
                                         ) - 1
                                ) + 0) AS varchar(max)) + 
             REVERSE(SUBSTRING(REVERSE(Declaration), 0, PATINDEX('%[0-9]%', REVERSE(Declaration)))) AS Decalration
FROM your_table
GROUP BY Code, 
         SUBSTRING(Declaration, 0, CHARINDEX('-', Declaration) + 2),
         REVERSE(SUBSTRING(REVERSE(Declaration), 0, PATINDEX('%[0-9]%', REVERSE(Declaration))))

Demo on SQLFiddle

sql query count the duplicates

3 Answers3