A count problem

MichaelHe

I have a little count problem.

My query looks like this:

$getlist=mysql_query("SELECT *,
DATE_FORMAT(p.OpretDato, '%d-%m-%Y') AS Dato,
DATE_FORMAT(p.SidsteLogOn, '%d-%m-%Y') AS SDato,
FROM Profiler p
ORDER BY p.ProfilID");

This is all very good.

But I have another simple table (Match) with a profilid and a costumerid looking like this ex.
profilid: 1 costumerid 12
profilid: 1 costumerid 18
profilid: 1 costumerid 33
profilid: 1 costumerid 47
profilid: 2 costumerid 14
profilid: 2 costumerid 24

It's very easy to count how many costumers each profil has, but how do I combine it with the first query.

I have combined them like this:

$getlist=mysql_query("SELECT *,
DATE_FORMAT(p.OpretDato, '%d-%m-%Y') AS Dato,
DATE_FORMAT(p.SidsteLogOn, '%d-%m-%Y') AS SDato,
FROM Profiler p LEFT JOIN
MATCH m ON (p.ProfilID = m.PID)
ORDER BY p.ProfilID");

With this I will get the data from Match, but I don't need the data from Match only the number of costumers this profil has.
I know about COUNT (*) but how do I combine it with the query ?

best
Michael

nemonoman

I think you want something like this:

SELECT Profiler.ProfilID , count(*)
FROM Profiler, Match
WHERE Profiler.ProfilID = Match.PID
GROUP BY ProfilID

Your example data would return:
1, 4
2, 2

MichaelHe

Thanks alot... now I got it :o)

But what's the difference between using JOIN and using something,something WHERE x=y

NogDog

The WHERE version is equivalent to doing an INNER JOIN -- only selecting rows where the specified column in the first table matches that of the column in the second table. The LEFT JOIN will include all rows/data from the first (left) table while only including those from the second table when the specified column values match.

nemonoman

My system has a ledger. I set up 33 account records for various transaction types. Some of these account types have never been used -- the accounts are there provisionally, but they have never been used. In the ledger, only 25 accounts have actually been used. The ledger records the account id of the transaction.

An INNER JOIN (or "Right Join") comes in 2 forms

Implied:
SELECT account.id, account.name, COUNT( * )
FROM account, ledger
WHERE ledger.accountid = account.id
GROUP BY account.id

Explicit:
SELECT account.id, account.name, COUNT( * )
FROM account
INNER JOIN ledger ON ledger.accountid = account.id
GROUP BY 1

Both these INNER JOINS return the same results:
25 rows only -- the rows where the 25 ledger accountids match up with account records

A LEFT JOIN (or "Outer Join") is in this form:

SELECT account.id, account.name, COUNT( * )
FROM account
LEFT JOIN ledger ON ledger.accountid = account.id
GROUP BY 1

This returns 33 rows: 1 row for ever account record. Where the ledger has any matching transactions, the number of records matches the INNER JOIN. For the 8 unmatched account records, the query returns "1"

If I explicitly request information from the related ledger rows:

SELECT account.id,
account.name,
COUNT( * ) ,
MAX( ledger.id )
FROM account
LEFT JOIN ledger ON accountid = account.id
GROUP BY 1

The field "MAX(ledger.id)" will be blank if there are no corresponding ledger records.

I can use this 'blank' to enhance the report:

SELECT account.id,
account.name,
IF( MAX( ledger.id ) IS NULL,
'NO LEDGER RECORDS',
COUNT( * ) )
FROM account
LEFT JOIN ledger ON accountid = account.id
GROUP BY 1

The query above checks to see if there are corresponding ledger records by testing the value of the MAX(ledger.id). If that value IS NULL (no ledger record), it returns the string "NO LEDGER RECORDS", otherwise, it returns the count of the ledger records.

The same sort of Outer Join logic could be applied to your problem.