Displaying a 0 value? 1

[shibuyauk]

My query here records how many individual clicks, each affiliate sends us per day.

SELECT cookiefrom, prodTypeID, COUNT(DISTINCT ipaddress) as indivclicks, COUNT(*) AS totalclicks
FROM applytracking
WHERE hitdate >= '20050101'
  AND hitdate < '20050623'
  
AND prodtypeid IN ('loans', 'Credit Cards', 'savings' , 'Current Accounts')


GROUP BY cookiefrom, prodTypeID

ORDER BY cookiefrom

It works great for those affiliates that actually did send us some clicks that day. But how can i display those who didnt send any as a 0?

Thanks

 

[jbenson001]

you would have to join this table to a user or accounts table that holds theri ip. Then you can use a Left Join. This will show the userID, ip or wht ever you want, even if they have now rows in the other table..

 

[NoCoolHandle]

SELECT cookiefrom, prodTypeID, COUNT(DISTINCT ipaddress) as indivclicks, COUNT(*) AS totalclicks
FROM applytracking 
WHERE hitdate >= '20050101'
  AND hitdate < '20050623'
  
AND prodtypeid IN ('loans', 'Credit Cards', 'savings' , 'Current Accounts')
[blue]
Union
SELECT "no Cookie", 0, COUNT(DISTINCT ipaddress) as indivclicks, 0 AS totalclicks
FROM applytracking 
WHERE ipaddress not in (SELECT ipaddress
FROM applytracking 
WHERE hitdate >= '20050101'
  AND hitdate < '20050623'  
AND prodtypeid IN ('loans', 'Credit Cards', 'savings' , 'Current Accounts')
)
and  prodtypeid IN ('loans', 'Credit Cards', 'savings' , 'Current Accounts')

[/blue]  
ORDER BY cookiefrom

That should do it..

Rob

 

[shibuyauk]

Wow that was fast, i can see the logic of what you are saying.

However i get the error

An error occured while executing the query
Column 'applytracking.cookieFrom' is invalid in the select list because it is not contained in an aggregate funtion and there is no GROUP BY clause

adding
GROUP BY cookiefrom, prodTypeID

gives the same message

 

[NoCoolHandle]

SELECT cookiefrom, prodTypeID, COUNT(DISTINCT ipaddress) as indivclicks, COUNT(*) AS totalclicks
FROM applytracking 
WHERE hitdate >= '20050101'
  AND hitdate < '20050623'
  
AND prodtypeid IN ('loans', 'Credit Cards', 'savings' , 'Current Accounts')
[blue]
Union [red]all[/red]
SELECT [red]cookiefrom[/red], 0, 0 as indivclicks, [red]0[/red] AS totalclicks
FROM applytracking 
WHERE ipaddress not in (SELECT ipaddress
FROM applytracking 
WHERE hitdate >= '20050101'
  AND hitdate < '20050623'  
AND prodtypeid IN ('loans', 'Credit Cards', 'savings' , 'Current Accounts')
)
and  prodtypeid IN ('loans', 'Credit Cards', 'savings' , 'Current Accounts')

[/blue]  
ORDER BY cookiefrom

I did think about joins, but think that the union might work faster.

Worth trying both approaches,but the union is an easy case of have one show what you did get and 1 the exclusions.

Of course that does require you do a full table scan. The first will proabably do an index search, but the second will not. It is so broard that you will necessarly look at all the data.

A workaround might be to have a table of just clientnames and then query your query (as a common table expression or temp table) to see what clients exist in it that don't exist in your hits table (which I am assuming is getting HUGE!!!)


For what it is worth

Rob

PS I added the Union "ALL" as there shouldn't be any duplicates to remove so this should run faster that the alternative.

 

[NoCoolHandle]

Crap... Just saw you second post.

Try adding back in the GroupBy statement before the order by

for somereason it got removed in some quick cut and paste action

 

[jbenson001]

Cool piece of logic!! took me a bit to understand it, but now I got it. I will keep this in mind if I need the same logic.

 

[shibuyauk]

Thanks NoCoolHandle, i think you are right about a seperate table of just clientnames. That would certainly produce faster results, as our hits table, as you say is getting huge!

Thanks for the tips!

 

[NoCoolHandle]

If it was helpfull I love to get paid in stars..

weak atempt at recognition.