1

I am working on a requirement where I need to get unique records count from one table with like search on the columns.

Here is the code:

Select 
    s1.so_number
from
(
Select (billto_name) As so_number 
from dbo.lamp_bookings 
where billto_name Like '%government%' or billto_name Like '%gov%' 
Union
Select (shipto_name) As so_number 
from dbo.lamp_bookings 
where shipto_name Like '%government%' or shipto_name Like '%gov%' 
Union
Select (soldto_name) As so_number 
from dbo.lamp_bookings
where soldto_name Like '%government%' or soldto_name Like '%gov%'
Union 
Select (end_user) As so_number
from dbo.lamp_bookings
where end_user Like '%government%' or end_user Like '%gov%'
) s1

I need to get distinct count from bookings either soldto_name or shipto_name or billto_name or end_user like (government, govt.,)

Improve this question
4
  • 2
    First figure out which RDBMS you're using. Then see meta.stackoverflow.com/questions/333952/… Commented Aug 17, 2018 at 23:09
  • I'm going to assume sql server since they included dbo. With that said, sample data and expected results would be very helpful. Commented Aug 17, 2018 at 23:11
  • You need COUNT (DISTINCT s1.so_number). Commented Aug 17, 2018 at 23:19
  • "government" contains "gov", so any string matching LIKE '%government%' also matches LIKE '%gov%'. You can shorten you WHERE clauses to WHERE <column> LIKE '%gov%'. Commented Aug 17, 2018 at 23:30

5 Answers 5

Reset to default
3

Here's one option that might be easier to manage using unpivot and count with distinct:

select count(distinct u.field)
from lamp_bookings
unpivot
(
  field
  for fields in (billto_name, shipto_name, soldto_name, end_user)
) u
where u.field like '%gov%'

Also you can remove your 2nd where criteria -- if you are checking for gov, no need to check for government as well.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for the reply, unpivot option works but there are almost 8 words i need to use search criteria and either any of these 8 searches exists it should return only one count. distinct records need be counted at last
@Harry - Just use or for each word you need to search - where u.field like '%gov%' or u.field like '%blah%' or ...
1

I would unpivot using values and then apply the logic:

select distinct v.so_number
from dbo.lamp_bookings b cross apply
     (values (billto_name), (shipto_name), (soldto_name), (end_user)
     ) v(so_number)
where v.so_number like '%government%' or v.so_number Like '%gov%' ;

If you just need the count, then use:

select count(distinct v.so_number)
from . . .
Improve this answer

Comments

0

Harry, Below query can be used. Assuming if Bill to name and ship to name has government it need to be considered as 1 row. Also I believe you are looking for count only.

Apologies, I am not able to add comments hence updating as answer. Let me know if you requirement is different. 

Select count (billto_name) 
from dbo.lamp_bookings 
where billto_name Like '%government%' or billto_name Like '%gov%' or shipto_name Like '%government%' or shipto_name Like '%gov%'  or soldto_name Like '%government%' or soldto_name Like '%gov%' or end_user Like '%government%' or end_user Like '%gov%';

Regards Jana

Improve this answer

2 Comments

This isn't removing duplicate names from the different columns.
Thanks for the reply, Yes you are right...either billto, shipto, enduser, soldto has any of the search terms like 'government', 'army', 'department','ministry' and each search term is considered only considered once. The code ypu mentioned worked but there are almost 8 search terms need to be added in the query.
0

You should put all where conditions in one big block. Then your don't need a subselect. It would also get rid of duplicates.

If, for example, end_user and billto_name both match the search string but are not exactly the same, then this row would be counted twice. I assume you don't want that. Because "union" would not merge them together, since it only merges exact duplicate rows.

Also, '%gov%' matches '%government%'. You could leave all the longer search strings away. But I am not sure if this is always possible in your overall use case.

Improve this answer

1 Comment

Thanks for reply, could you please help me in writing the code
0

Use SELECT COUNT(*) instead of SELECT s1.so_number.

You don't need to use COUNT(DISTINCT so_number), since UNION automatically removes duplicates from the result.

There's also no need to use both LIKE '%government%' and LIKE '%gov%'. If a string contains government, it must also contain gov.

Improve this answer

4 Comments

… but the aggregate is part of each single select which is calculated before the UNION...
@WolfgangKais There's no aggregation in the individual selects.
Those 2 queries can produce different results. I thought the same at first, but you have to count each potential column, not count on 1 column only. See this demo for what I mean: rextester.com/NVEWH64179
@sgeddes You're right, I didn't think of the case where a single row contributes multiple names to the result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.