Counting records

[mattyvx]

Hello all i'm having some problems counting the most common records from a table.

 

SQL VERSION: 5.0.91

 

The following table records referalls from my website to my members sites. An auto incrementing RefID is stored aswell as the Member ID and the DateTime the referal was sent.

 

Table: R_Website

RefID  MemID  Date

1          112    27-12-2010

2          2        "

3          3        "

4          112      "

5          3        "

6          112      "

 

What I want to do is produce a table which looks like this;

 

MemID  Referals

112            3

3                2

2                1

 

 

Any ideas?

 

Thanks in advance!

[DavidAM]

-- Basic query
SELECT memID, COUNT(*) AS Referals 
FROM R_Website 
GROUP BY memID
ORDER BY COUNT(*) DESC

-- Add this (after FROM clause) to limit to a specific date
WHERE `Date` = '2010-12-27' 

-- Add this (after the GROUP BY phrase) to get only those with more than 1 referral
HAVING COUNT(*) > 1

-- Add this (at the very end) For a Top 10 List
LIMIT 10

 

So, a TOP 10 List of multiple referrals for Today would be:

SELECT memID, COUNT(*) AS Referals 
FROM R_Website 
WHERE `Date` = '2010-12-27' 
GROUP BY memID
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
LIMIT 10

 

Note: I assumed your Date field is defined as an DATE (or DATETIME) field in the database.

 

[mattyvx]

Great! a little tweaking and I now have top three members for the last month.

 

Now to make things a little more complicated;

 

I have two tables, one which tracks website referals and one which tracks profile views.

 

Website                              Profile

RefID    MemID    Date                  VisitID      MemID      Date   

1            112                                      1            42

2              2                                        2            112

3              3                                        3            112

4            112                                      4              2

5              3                                        5              2

6            112                                      6              2

 

What I would like is;

 

MemID  Web    Profile  Total

112        3            2        5

3            2            0        2

2            1            3        4

42          0            1        1

 

Is this possible from one query? I know I can run two queries and get the result I want using PHP but I'd prefer to have one SQL statement. I run into problems when having two Count(*) in the select statement :S

 

Cheers! 

[mattyvx]

forgot to add, the data should be from the last month only!

[Maq]

forgot to add, the data should be from the last month only!

 

Did you solve the issue you were having?

[DavidAM]

Since any kind of join will likely include multiple occurrences of the same row from one table or the other, I would tackle this as a union. So first, the two queries needed to get the data:

SELECT memID, COUNT(RefID) FROM Website 
WHERE DATE(`Date`) = '2010-12-27' GROUP BY memID

-- AND

SELECT memID, COUNT(VisitID) FROM Profile
WHERE DATE(`Date`) = '2010-12-27' GROUP BY memID

Note 1: Just to make the intention clear, I count the primary key instead of '*'. This may improve performance on this type of query in general anyway (depending on your indexes).

Note 2: I changed the date selection slightly, if the column 'Date' is a DATETIME type, we need to look at only the date part. I forgot about this in my previous response. Although, if the column is indexed, using BETWEEN (i.e. Date BETWEEN '2010-12-27' AND '2010-12-27 23:59:59') may allow the server to use the index whereas (I think) the above form will force a table scan.

 

Now we can create a union from these and with a little magic, get your desired results:

SELECT memID, SUM(RefCnt) AS Referals, SUM(VisCnt) AS Visits, SUM(RefCnt + VisCnt) AS Total 
FROM (
  SELECT memID, COUNT(RefID) AS RefCnt, 0 AS VisCnt FROM Website 
   WHERE DATE(`Date`) = '2010-12-27' GROUP BY memID
UNION
  SELECT memID, 0, COUNT(VisitID) FROM Profile
   WHERE DATE(`Date`) = '2010-12-27' GROUP BY memID) AS Counter 
GROUP BY memID

 

Since all SELECTs in a UNION must return the same number of columns, with the same data types in the same order, we added the 'Visits' count into the first SELECT as '0' (zero). Then we added the Referals count in the second SELECT as zero. We don't have to alias the columns in the second SELECT because the column names in a UNION will come from the first SELECT statement.

 

When you tweak this, tweak the inner SELECTs to limit the data you are counting (i.e. WHERE). Tweak the outer statement to limit the data returned (i.e. HAVING, LIMIT)

 

I don't have an environment to test with here at the office, so you may have to work with that a bit (see MySql.com for syntax).

 

Oops, I missed the part about last month. Rather than use DATE() functions on a column in the WHERE clauses, I would determine the Start and End dates in PHP and use BETWEEN in the SQL. As I said, this will allow the server to use an index (if one is defined) on the Date column. For pure SQL, you could determine these values using some of the many DATE functions in SQL. Maybe something like:

 

WHERE `Date` BETWEEN DATE_FORMAT(NOW(), '%Y%m01') AND DATE_FORMAT(LAST_DAY(NOW()), '%Y%m%d235959')

[mattyvx]

Brilliant, thanks for your help {SOLVED!}