advanced sql question

[tim_s]

hi.

i've got a sql report to produce but i've got no idea how to go about it.

i have 3 tables Machine, User and Wins.

the Wins table has 2 foreign keys which links to 1 record in the User table and 1 in the Machine table.

I need to produce a query which lists machines with 3 columns which are the number of wins for certain date range which are calculated on user.DateOfBirth.

i.e.

Number of plays per machine

Machine---0-17 years-----18-21 years-----21 years+

Machine 1---50----1000-----12
Machine 2---0------5-------299

hope that makes sense!

does anyone know how i can do this or where i can find out?

[Don]

go on. post up the definitions of the tables and the indexes...


I reckon you can do this in one statement.

There is nothing that can't be achieved with

CASE
WHEN thing THEN otherthing
WHEN adifferentthing THEN anotherthingagain
END AS THINGY

and some SUM and GROUP BY

[Don]

.SELECT M.MACHINE_NR,
. SUM(
. CASE
. U.DOB <= 17 THEN 1
. ELSE 0
. END AS ZEROTO17),
. SUM(
. CASE
. U.DOB >17 AND U.DOB <= 21 THEN 1
. ELSE 0
. END) AS EIGHTEENTO21,
. SUM(
. CASE
. U.DOB >21 THEN 1
. ELSE 0
. END) AS OVER21
.FROM MACHINE M
. INNER JOIN WINS W ON (W.MACHINE_NR = M.MACHINE_NR)
. INNER JOIN USER U ON (U.USER_NR = W.USER_NR)
.GROUP BY M.MACHINE_NR

>> Edited by Don on Wednesday 13th October 16:14

[JonRB]

Blimey Don. I had no idea you had guruistic* tendencies.

(* - See, it's not just monkey-man Bush who can make up words on the spot. )

>> Edited by JonRB on Wednesday 13th October 16:19

[tim_s]

Don ur a legend!!!

I've just managed to do it using subqueries but even with 10 machines and a few thousand win records it's taking a second!

i'll have a go at implementing your sql.

[Don]

JonRB said:

Blimey Don. I had no idea you had guruistic* tendencies.

tim_s said:

Don ur a legend!

Hope it works!

>> Edited by Don on Wednesday 13th October 16:35