# sql, union, count and group by



## Aberbotimue (Feb 23, 2009)

Hi guys, I am hoping the strapline on the home page is taken into account here

_"There's no such thing as a stupid question, but they're the easiest to answer!"_

I am confident this is a simple question, but my SQL is that bad, I have been googleing for ages, and still getting more errors than I started with!!

what I have, is a very simple produt table, and i want to write a very crude search engine for the product items..

a simple

```
item like '%searchstring%'
```
 fails when you go for two worded searches.

so I was looking at somthing like


```
SELECT     *
FROM         items
WHERE     (item LIKE '%nokia%')
union
SELECT     *
FROM         items where
(item LIKE '%original%')
```
allowing a results that have both words, in any order..

My next thinking was, make it a union ALL to give me dupes, and then count the dupes, and order by a count on the dupes, so that the item,s that have more than one of the words in, will gravitate to the top of the list??

Does that make sense!!

the moment I put the count ( itemid) in there, it winged about group by - and then when I added that, all hell broke loose..

any advice would be greatfull..

Nathan
(aberbotimue)


----------



## DoubleHelix (Dec 10, 2004)

```
select * from items where item like '%nokia%' and item like '%original%'
```


----------



## Aberbotimue (Feb 23, 2009)

DoubleHelix said:


> ```
> select * from items where item like '%nokia%' and item like '%original%'
> ```


I still want the items that only have one of them in the name, but the ones with two nearer the top, hence ordering by the number of duplicates, and doing specific searhes on each word..

i.e.

a search for "original nokia" would yeild

the original bing bang nokia
nokia original buttons
nokia phone
original hits from abba


----------

