2. PHPRunner General questions
  3. Problem with advanced search with a count
This topic is locked

Problem with advanced search with a count

 		1/15/2009 10:28:53 AM
PHPRunner General questions
P
 paolobg author

I have a problem. I create my view:

SELECT

imp.societa_id,

status,

dominio,

online as data_online,

dataora as data_iscrizione,

online_dominio as data_online_dominio,

COUNT(DISTINCT cookieId) as visitatori_unici,

COUNT(cookieId) as pagine_viste

FROM gioca_sitelli.sitello as sit, gioca_report.2009_01_impression_sito as imp

WHERE sit.id = imp.societa_id AND imp.stato = "PASS"

GROUP BY imp.societa_id


I allow adv search but if i try to set a value i have this error:

Invalid use of group function

Query SQL:
SELECT DISTINCT COUNT(cookieId) FROM gioca_sitelli.sitello as sit, gioca_report.2009_01_impression_sito as imp WHERE (sit.id = imp.societa_id AND imp.stato = "PASS" ) and ( COUNT(cookieId) like '%76%')GROUP BY imp.societa_id ORDER BY 1 LIMIT 10


The error is ( COUNT(cookieId) like '%76%')GROUP BY imp.societa_id because is in where because there is a group by. How can change the preimposte code for set this in having?
thanks a lot.
T
 		thesofa 1/15/2009

I have had the same problem many times, you cannot use one of the group functions in a where clause, you need to modify the sql query so it reads

HAVING COUNT(cookieId) like '%76%'



just before the order part

Check the mysql syntax for 'Having'
P
 		paolobg author 1/15/2009

thanks thesofa for the reply and your help.
I know it. If i use a count, the condition for it, i must write in a having. The problem is that the query is autogenerated.
It's a result of advanced search on a "COUNT(cookieId)". COUNT(cookieId) is a colon of my view. I have a troble because i don't know how change the code into the advanced search of view because is javascript.
Is there anyone that do it?
Thanks a lot
T
 		thesofa 1/16/2009

OK, I knew I had posted about this before.

Read this thread and you will find the answer, except I cannot be certain it works with the latest version of PHPR.

You need to read very carefully all of the thread, because there was quite a bit of misunderstanding early on, but I am sure you will be able to get the answer from my post.

HTH