Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Help with a UNION query

Codes here !

Moderators: macek, egami, gesf

Help with a UNION query

Postby fluff » Tue Apr 29, 2003 2:14 am

I am trying to retrieve a count for occurrences of values in a column from a table.

The following query:

SELECT COUNT( page_id ) AS index_count FROM mm_click_count WHERE page_id ='index'

Work fine for one vale (index), but I want to return values for all variables in separate columns in the result table. I have tried using a union..

SELECT COUNT( page_id ) AS index_count FROM mm_click_count WHERE page_id ='index'
UNION
SELECT COUNT( page_id ) AS forward_count FROM mm_click_count WHERE page_id =’forward'

But this produces an arror. Is there something wrong with the syntax, or is there another way of getting these results??

Thanks
:?:
fluff
New php-forum User
New php-forum User
 
Posts: 7
Joined: Thu Dec 19, 2002 8:43 am

Postby Redcircle » Tue Apr 29, 2003 2:20 am

What version of MySQL are you using. UNION is not supported prior to 4.0.12 which just hit production specification. Most likely you are using 3.x which does not support it.
User avatar
Redcircle
Moderator
Moderator
 
Posts: 830
Joined: Tue Jan 21, 2003 10:42 pm
Location: Michigan USA

Postby fluff » Tue Apr 29, 2003 2:24 am

running 3.23.54 :(

any idea how to aceive this with this version? I assume I would have to use a LEFT JOIN? any idea how to do this with a count, I did try, but I don't think my syntax was correct?

Tkx
fluff
New php-forum User
New php-forum User
 
Posts: 7
Joined: Thu Dec 19, 2002 8:43 am

Postby Redcircle » Tue Apr 29, 2003 2:58 am

I have tried with no success. All the tutorials I've read have not worked.


Well.. Because I've never used 4.0.12 i've never been able to mess with a UNION statement so I'm not sure if it's correct :)
User avatar
Redcircle
Moderator
Moderator
 
Posts: 830
Joined: Tue Jan 21, 2003 10:42 pm
Location: Michigan USA

Postby fluff » Tue Apr 29, 2003 3:05 am

Anyone else know how I can get a result set, containing a count of instances of variables in a column from one table.

SELECT COUNT( page_id ) AS index_count FROM mm_click_count WHERE page_id ='index'

gives me one of the values, I also need a count for page_id ='forward' for example ??

Thanks in advance :!:
fluff
New php-forum User
New php-forum User
 
Posts: 7
Joined: Thu Dec 19, 2002 8:43 am

Postby Redcircle » Tue Apr 29, 2003 3:09 am

You might beable to do it by creating a temporary table. But it is VERY taxing on the server.
User avatar
Redcircle
Moderator
Moderator
 
Posts: 830
Joined: Tue Jan 21, 2003 10:42 pm
Location: Michigan USA

Postby fluff » Tue Apr 29, 2003 3:49 am

:cry:

I managed to get a friends help on the SQL, he suggested the following:

SELECT COUNT( TABLE1.page_id ) AS index_count,
COUNT( TABLE2.page_id ) AS forward_count
FROM (SELECT * FROM mm_click_count WHERE page_id ='index' ) TABLE1,
(SELECT * FROM mm_click_count WHERE page_id ='forward' ) TABLE2

but this doesn't seem to work on MySQL, and I though I was getting close :o(
fluff
New php-forum User
New php-forum User
 
Posts: 7
Joined: Thu Dec 19, 2002 8:43 am

Postby fluff » Tue Apr 29, 2003 4:57 am

through a great deal of beer trading, i managed to get a guru's help. in case you fall over the same problem, here's some code that works..

SELECT SUM(CASE page_id WHEN 'index' THEN 1 ELSE 0 END) AS index_count,
SUM(CASE page_id WHEN 'forward' THEN 1 ELSE 0 END) AS forward_count
from table01
fluff
New php-forum User
New php-forum User
 
Posts: 7
Joined: Thu Dec 19, 2002 8:43 am


Return to mySQL & php coding

Who is online

Users browsing this forum: Yahoo [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.