CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3906930
user3906930

Reputation: 77

SQL - Count variances within in a column

I have a bit of a dilemma.

In my DB, I have Box Numbers that contain,

“13NR0123-0001, 13NR0123-0002….“

and other box numbers that don’t.

“13NR0456”

The reason for the

-0001, -0002

is that these boxes has subs in the boxes and the other don’t have subs.

I’m trying to ascertain how many boxes I have in my Database, I have written two scripts for each variance but its not giving me the correct answer.

The below is for report for sub boxes

select distinct substring(FD_E77BE253,1,len(FD_E77BE253) - 5) as BoxNumber,
count(FD_84A4EF1A) As Document_Count
from FD_Documents
where deleted = '0'
and FD_9DAADEC8 is not null
and FD_E77BE253 like '%-%'
group by FD_E77BE253

The below is for no sub-boxes

select distinct (FD_E77BE253) as BoxNumber,
count(FD_84A4EF1A) As Document_Count
from FD_Documents
where deleted = '0'
and FD_9DAADEC8 is not null
and FD_E77BE253 not like '%-%'
group by FD_E77BE253

The script with no subs work fine, but the one with sub-boxes does not give me accurate figures.

Is there anyway I can combine both??

Expected output must look like the below enter image description here

Thanking you in advance.

Upvotes: 0

Views: 241

Answers (2)

Siavash Rostami
Siavash Rostami

Reputation: 1933

The reason why your first query doesn't work is that you're grouping on FD_E77BE253 but the field on your select is a substring of the whole, so basically you're grouping on some other data and projecting a sub portion of it. the count belongs to the whole thing not the substring, that why.

In order to fix that you have to create a temp table containing refined data OR write a common table expression for refined data and use it as the source of your final query.

it goes something like this:

;with cte1 as (
select substring(FD_E77BE253,1,len(FD_E77BE253) - 5) as BoxNumber, FD_84A4EF1A As Document_Count
from FD_Documents
where deleted = '0'
and FD_9DAADEC8 is not null
and FD_E77BE253 like '%-%'
),
cte2 as (
select distinct (FD_E77BE253) as BoxNumber, FD_84A4EF1A As Document_Count
from FD_Documents
where deleted = '0'
and FD_9DAADEC8 is not null
and FD_E77BE253 not like '%-%'
)

    select distinct cte1.BoxNumber, count(cte1.Document_Count)
    from cte1
    group by BoxNumber
union 
    select distinct cte2.BoxNumber, count(cte2.Document_Count)
    from cte2
    group by BoxNumber

Upvotes: 1

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Just add '-' and use one query to count:

select LEFT(FD_E77BE253 + '-', CHARINDEX('-', FD_E77BE253 + '-') - 1) as BoxNumber,
    count(FD_84A4EF1A) As Document_Count
from FD_Documents
where deleted = '0'
and FD_9DAADEC8 is not null
group by FD_E77BE253

Upvotes: 0

Related Questions