[SOLVED] Struggling with an average kinda thing...!

[ojs]

Hi

 

I have two tables, one called users, one called fans.

 

Users:

username, joindate

 

Fans:

username, fanof

 

 

A user can become a fan of another user, so:

 

Users:

Bob, 2009-01-14

Bill, 2009-01-14

Rita, 2009-01-12

Sue, 2009-01-05

 

Fans:

 

Bob, Sue

Bob, Rita

Bill, Bob

Sue, Rita

 

 

What I'm trying to do is calculate the average number of fans for each user since they joined.

 

i.e. Rita has two fans (Sue and Bob) since she joined 2 days ago, therefore she has an average fan score of 1 (2 fans in 2 days)

 

I have this sql statement which works:

 

SELECT users.username AS NAME FROM users ORDER BY (SELECT ((SELECT count(*) as fans FROM fans WHERE fanof =NAME)/(SELECT DATEDIFF(NOW(), users.joindate) as DiffDat FROM users WHERE username=NAME)) AS average FROM users WHERE username = NAME) DESC

 

and produces a list of names in the right order..Great!

 

But, I want it to produde a list of names AND the average fans they have.

 

I have no idea.

 

Please help.

[rhodesa]

how about this:

SELECT f.fanof, COUNT(f.username) as numfans, (DATEDIFF(NOW(),u.joindate)) as numdays, COUNT(f.username)/(DATEDIFF(NOW(),u.joindate)) as fansperday FROM fans f LEFT JOIN users u ON f.fanof = u.username GROUP BY f.fanof

[ojs]

Thanks for the reply...

 

But I'm not sure what I'm supposed to do with it!

 

Imagine I'm a bit of a drongo (not too tough), and could you paste the complete query which I could run?

 

Thanks

 

 

[rhodesa]

you should be able to run that query...if the tables are structured as listed. i used the following table structure and data:

CREATE TABLE `users` (
  `username` varchar(255) NOT NULL,
  `joindate` date NOT NULL,
  PRIMARY KEY  (`username`)
);
INSERT INTO `users` (`username`, `joindate`) VALUES 
('Bill', '2009-01-14'),
('Bob', '2009-01-14'),
('Rita', '2009-01-12'),
('Sue', '2009-01-05');

CREATE TABLE `fans` (
  `username` varchar(255) NOT NULL,
  `fanof` varchar(255) NOT NULL
);
INSERT INTO `fans` (`username`, `fanof`) VALUES 
('Bob', 'Sue'),
('Bob', 'Rita'),
('Bill', 'Bob'),
('Sue', 'Rita');

used this query:

SELECT
    u.username, 
    COUNT(f.username) as numfans,
    DATEDIFF(NOW(),u.joindate) as numdays,
    COUNT(f.username)/(DATEDIFF(NOW(),u.joindate)) as fansperday
  FROM fans f LEFT JOIN 
    users u ON f.fanof = u.username
  GROUP BY u.username

and got the following:

username  numfans  numdays  fansperday

Bob      1        0        NULL

Rita      2        2        1.0000

Sue      1        9        0.1111

[ojs]

Fantastic!

 

Case closed.