Link to home
Create AccountLog in
Avatar of Coast Line
Coast LineFlag for Canada

asked on

trouble with the counts again in mysql query

I am low in sql, So experts can guide me!

I have the following query which is returning counts wrong:

here is the query:

SELECT categories.category,categories.catdepth,categories.categoryID, recently_searched,  
  case when IsNull(recently_searched,'') != '' then COUNT(distinct companycategories.catID) else 0 end as Total  
  FROM categories
  inner join companycategories on companycategories.catID = categories.categoryid  
  where categories.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.keyword#%">)
  OR categories.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#%">)
  OR categories.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#">)
  Group by categories.categoryid,categories.category,catorder,catdepth, recently_searched    
  ORDER BY catorder

so records are total 10, but in counts, it shows 1 or 0, please guide

here is the url that can tell

http://bit.ly/hXN0Nq
Avatar of Sharath S
Sharath S
Flag of United States of America image

try this
select category,catdepth,categoryID,recently_searched,count(distinct Total) as Total from (
SELECT categories.category,categories.catdepth,categories.categoryID, recently_searched,  
  case when IsNull(recently_searched,'') != '' then companycategories.catID else 0 end as Total  
  FROM categories 
  inner join companycategories on companycategories.catID = categories.categoryid  
  where categories.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.keyword#%">) 
  OR categories.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#%">) 
  OR categories.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#">)) as t1 
  Group by category,catdepth,categoryID,recently_searched

Open in new window

>>Zones: MS SQL Server, Databases Miscellaneous, Cold Fusion Markup Language<<
Please request that the MS SQL Server zone be replaced with the more appropriate MySQL zone:
https://www.experts-exchange.com/Database/MySQL/
Avatar of Coast Line

ASKER

nope, that did solved the issue, i am facing the same issue here
Can you post the result of this query and your expected result?
SELECT categories.category,categories.catdepth,categories.categoryID, recently_searched,  
  companycategories.catID
  --case when IsNull(recently_searched,'') != '' then COUNT(distinct companycategories.catID) else 0 end as Total  
  FROM categories 
  inner join companycategories on companycategories.catID = categories.categoryid  
  where categories.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.keyword#%">) 
  OR categories.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#%">) 
  OR categories.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#">) 
  --Group by categories.categoryid,categories.category,catorder,catdepth, recently_searched    
  ORDER BY catorder

Open in new window

running this query did returned around 64 records while in the url i have shown that if the records shown in the middle part of 13, it should show 13 on left bar may be depepnding on various categories counts like

advertising(3)
banners(10)

both are interrelated and the value 'advertising' exists in both categories somehow
abc.csv
Did not understand anything from your excel sheet.
Actaully, have you seen the link i showed above..

in that link. records are shown 13 but on the left side, the counts are only 1,1,1,1 in three/four categories which are somehoe associated with it

so the middle counts must match the left counts in a way that both should show eual counts, eual counts i mean that on the middle part, it shows 13, on the left part, if there are 3 categories, the counts shoulf of the all the three should come to 13,

That is my issue!
any update experts
I am not able to access the URL.
url works check now

I lost you.
>> in that link. records are shown 13 but on the left side, the counts are only 1,1,1,1 in three/four categories which are somehoe associated with it

I did not find this information in the page.
which page you want information,
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks Ste5an