please need help with this query!!

[KaKoRoT]

Hi guys, bare with me I have not done any sql coding since when i was at uni,

 

now i can only remember the basics, and all the neat tricks i taught my self are forgotten!!

 

could you help me clean this up as well as get it work properly.

 

//Code Start

 

SELECT sum( swtickettimetrack.timespent ) AS timespent, swtickettimetrack.ticketid, swtickets.ticketid, swtickets.userid, swusers.usergroupid, swusergroups.title, swusergroups.usergroupid

 

FROM swtickets, swusers, swusergroups, swtickettimetrack

 

WHERE swtickettimetrack.ticketid = swtickets.ticketid

 

AND swtickets.userid = swusers.userid

AND swusers.usergroupid = swusergroups.usergroupid

AND swusergroups.title = 'PHASCA'

 

GROUP BY swtickettimetrack.timespent

 

//Code End

 

cant remember how to get it to only display the sum value, I am sure it was something like this (oh yeah and something about alias')

 

SELECT sum( swtickettimetrack.timespent ) AS timespent

FROM swtickettimetrack

 

WHERE (SELECT swtickettimetrack.ticketid, swtickets.ticketid, swtickets.userid, swusers.usergroupid, swusergroups.title, swusergroups.usergroupid

 

FROM swtickets, swusers, swusergroups, swtickettimetrack

 

AND swtickettimetrack.ticketid = swtickets.ticketid

AND swtickets.userid = swusers.userid

AND swusers.usergroupid = swusergroups.usergroupid

AND swusergroups.title = 'PHASCA')

 

GROUP BY swtickettimetrack.timespent;

 

Some hints or tips to help me remember would be nice thanks!!!

[kickstart]

Hi

 

Think you need to do it this way:-

 

SELECT timespent
FROM (
SELECT sum( swtickettimetrack.timespent ) AS timespent, swtickettimetrack.ticketid, swtickets.ticketid, swtickets.userid, swusers.usergroupid, swusergroups.title, swusergroups.usergroupid

FROM swtickets
JOIN swtickettimetrack ON swtickettimetrack.ticketid = swtickets.ticketid
JOIN swusers ON swtickets.userid = swusers.userid
JOIN swusergroups ON swusers.usergroupid = swusergroups.usergroupid

WHERE  swusergroups.title = 'PHASCA'

GROUP BY swtickettimetrack.ticketid, swtickets.ticketid, swtickets.userid, swusers.usergroupid, swusergroups.title, swusergroups.usergroupid)
[code]

All the best

Keith

[KaKoRoT]

Hi Keith, thanks for replying ... unfortunately it doesnt work, I understand why though, but its the reason for it dont work is what i cant remember to do.. "aliases"

 

here is the error..

 

Error

 

SQL query: Documentation

 

SELECT timespent

FROM (

 

SELECT sum( swtickettimetrack.timespent ) AS timespent, swtickettimetrack.ticketid, swtickets.ticketid, swtickets.userid, swusers.usergroupid, swusergroups.title, swusergroups.usergroupid

FROM swtickets

JOIN swtickettimetrack ON swtickettimetrack.ticketid = swtickets.ticketid

JOIN swusers ON swtickets.userid = swusers.userid

JOIN swusergroups ON swusers.usergroupid = swusergroups.usergroupid

WHERE swusergroups.title = 'PHASCA'

GROUP BY swtickettimetrack.ticketid, swtickets.ticketid, swtickets.userid, swusers.usergroupid, swusergroups.title, swusergroups.usergroupid

)

 

MySQL said: Documentation

#1248 - Every derived table must have its own alias

[kickstart]

Hi

 

Not 100% sure, but just add "AS fred" (or any other name) to the end of the statement (after the last bracket) and that should fix it.

 

All the best

 

Keith