We have a search facility on our website that returns products and articles related to the search term in 2 separate queries.

To improve accuracy we've added a keyword table so we can link specific search terms to a product or article.

Table structure is:

So a search term can be linked to a product or help article.

On the admin side we have a page that lists the most recently added keywords, with a count of how many products and articles they link to.

I tried this query, but the counts were incorrect as they included NULL or blank rows:

SELECT MAX(id) as maxid, keyword, 
COUNT(product) AS prodcount, COUNT(helpid) AS helpcount 
FROM keyword_table 
GROUP BY keyword 
ORDER BY maxid DESC 
LIMIT 40

So I switched to conditional SUM instead of COUNT:

SELECT MAX(id) as maxid, keyword, 
SUM(if(product != '', 1, 0)) AS prodcount, SUM(if(helpid != '', 1, 0)) AS helpcount 
FROM keyword_table 
GROUP BY keyword 
ORDER BY maxid DESC
LIMIT 40

This appears to give the correct values, but with this warning:

Warning: Truncated incorrect DOUBLE value: ''

Every article I've found on that error relates to UPDATE queries, not SELECT.

Please does anyone have any suggestions?

CodePudding user response：

you should filter your results with a WHERE, doing it with an IF is what give you warnings most probably.

Try it like this if the following brings the same results(with nulls) change IS NOT NULL to != '' and try agian ..

SELECT MAX(id) as maxid, keyword, 
COUNT(product) AS prodcount, COUNT(helpid) AS helpcount 
FROM keyword_table 
WHERE product IS NOT NULL OR helpid IS NOT NULL 
GROUP BY keyword 
ORDER BY maxid 
DESC LIMIT 40

CodePudding user response：

To exclude nulls or empty set you can apply case condition.

Something like:

SELECT MAX(id) as maxid, 
       keyword, 
       SUM(case when product is not null and product !='' then 1 else 0 end) AS prodcount, 
       SUM(case when helpid is not null and helpid !='' then 1 else 0 end) AS helpcount 
FROM keyword_table 
GROUP BY keyword 
ORDER BY maxid 
DESC LIMIT 40