CODEWITHSUNDEEP
sqlsql-server
Harry
Harry

Reputation: 13

How to write SQL subqueries?

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.,)

Upvotes: 1

Views: 103

Answers (5)

sgeddes
sgeddes

Reputation: 62831

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.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269533

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 . . .

Upvotes: 1

Barmar
Barmar

Reputation: 780724

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.

Upvotes: 0

Peter Zilz
Peter Zilz

Reputation: 51

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.

Upvotes: 0

Jana
Jana

Reputation: 11

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

Upvotes: 0

Related Questions