COUNT from 2 tables

COUNT from 2 tables

am 08.07.2009 21:21:16 von B

I'm trying to get a count for columns in 2 tables at once. I have
sessions and downloads tables and would like to get some basic stats.

mysql> describe sessions;
+---------+------------------+------+-----+---------+------- ---------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+------- ---------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| created | datetime | YES | | NULL | |
| user_id | int(10) unsigned | NO | MUL | NULL | |
+---------+------------------+------+-----+---------+------- ---------+
3 rows in set (0.01 sec)

mysql> describe downloads;
+--------------+------------------+------+-----+---------+-- --------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-- --------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| created | datetime | YES | | NULL | |
| user_id | int(10) unsigned | NO | MUL | NULL | |
| item_file_id | int(10) unsigned | NO | MUL | NULL | |
| session_id | int(10) unsigned | NO | | NULL |
|
| path | text | NO | | NULL | |
+--------------+------------------+------+-----+---------+-- --------------+
6 rows in set (0.01 sec)

mysql> SELECT MONTHNAME(created) AS month, COUNT(*) AS num_logins FROM
sessions GROUP BY month;
+-------+------------+
| month | num_logins |
+-------+------------+
| July | 6 |
| June | 214 |
| May | 150 |
+-------+------------+
3 rows in set (0.00 sec)

mysql> SELECT MONTHNAME(created) AS month, COUNT(*) AS num_downloads
FROM downloads GROUP BY month;
+-------+---------------+
| month | num_downloads |
+-------+---------------+
| June | 389 |
| May | 220 |
+-------+---------------+
2 rows in set (0.01 sec)


In trying to get the count from both tables at once, the logins are no
longer being summed as expected:

mysql> SELECT MONTHNAME(s.created) AS month, COUNT(s.id) AS num_logins,
COUNT(d.id) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d
ON d.session_id = s.id GROUP BY month;
+-------+------------+---------------+
| month | num_logins | num_downloads |
+-------+------------+---------------+
| July | 6 | 0 |
| June | 539 | 389 |
| May | 350 | 220 |
+-------+------------+---------------+
3 rows in set (0.31 sec)


Is this possible to do without using a sub-query?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: COUNT from 2 tables

am 08.07.2009 21:33:03 von Gary Smith

Off the top of my head, try this.

SELECT=20
MONTHNAME(s.created) AS month,=20
sum(if(ifnull(s.id,0) > 0, 1, 0)) AS num_logins,
sim(if(ifnull(d.id, 0)> 0, 1, 0)) AS num_downloads=20
FROM sessions AS s LEFT JOIN downloads AS d
ON d.session_id =3D s.id GROUP BY month

________________________________________
From: b [mysql@logi.ca]
Sent: Wednesday, July 08, 2009 12:21 PM
To: mysql@lists.mysql.com
Subject: COUNT from 2 tables

I'm trying to get a count for columns in 2 tables at once. I have
sessions and downloads tables and would like to get some basic stats.

mysql> describe sessions;
+---------+------------------+------+-----+---------+------- ---------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+------- ---------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| created | datetime | YES | | NULL | |
| user_id | int(10) unsigned | NO | MUL | NULL | |
+---------+------------------+------+-----+---------+------- ---------+
3 rows in set (0.01 sec)

mysql> describe downloads;
+--------------+------------------+------+-----+---------+-- --------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-- --------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| created | datetime | YES | | NULL | |
| user_id | int(10) unsigned | NO | MUL | NULL | |
| item_file_id | int(10) unsigned | NO | MUL | NULL | |
| session_id | int(10) unsigned | NO | | NULL |
|
| path | text | NO | | NULL | |
+--------------+------------------+------+-----+---------+-- --------------+
6 rows in set (0.01 sec)

mysql> SELECT MONTHNAME(created) AS month, COUNT(*) AS num_logins FROM
sessions GROUP BY month;
+-------+------------+
| month | num_logins |
+-------+------------+
| July | 6 |
| June | 214 |
| May | 150 |
+-------+------------+
3 rows in set (0.00 sec)

mysql> SELECT MONTHNAME(created) AS month, COUNT(*) AS num_downloads
FROM downloads GROUP BY month;
+-------+---------------+
| month | num_downloads |
+-------+---------------+
| June | 389 |
| May | 220 |
+-------+---------------+
2 rows in set (0.01 sec)


In trying to get the count from both tables at once, the logins are no
longer being summed as expected:

mysql> SELECT MONTHNAME(s.created) AS month, COUNT(s.id) AS num_logins,
COUNT(d.id) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d
ON d.session_id =3D s.id GROUP BY month;
+-------+------------+---------------+
| month | num_logins | num_downloads |
+-------+------------+---------------+
| July | 6 | 0 |
| June | 539 | 389 |
| May | 350 | 220 |
+-------+------------+---------------+
3 rows in set (0.31 sec)


Is this possible to do without using a sub-query?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgary@primeexalia.co=
m=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: COUNT from 2 tables

am 08.07.2009 21:55:34 von B

On 07/08/2009 03:33 PM, Gary Smith wrote:
> Off the top of my head, try this.
>
> SELECT
> MONTHNAME(s.created) AS month,
> sum(if(ifnull(s.id,0)> 0, 1, 0)) AS num_logins,
> sim(if(ifnull(d.id, 0)> 0, 1, 0)) AS num_downloads
> FROM sessions AS s LEFT JOIN downloads AS d
> ON d.session_id = s.id GROUP BY month
>

Nope, I'm still getting those same incorrect sums. Thanks, though. It
seems to me that the problem is that I'm grouping by the month for one
table but counting from both.

I'd paste the output here but I just upgraded Fedora and the BETA (wtf?)
version of Thunderbird crashes when I paste into an email (how the
earlier paste worked I don't know).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Replication switch Master to slave and back

am 08.07.2009 22:38:50 von bcantwell

I have successfully set up a replication master/slave scenario with my =
MySQL 5.0.51a
Now in the event of a fail over to the slave (an identical appliance), I =
want the old master to become the slave for an eventual switch back the =
other way.
Would it really take the same process to keep the old master up to date =
with the new one? Or is there a way to bring it up to date with the new =
machine without a mysqldump or copying data files?=20

I have binary logging running on both machines in hopes that I could =
just tell the new slave how to catch up with the new master...
Any assistance here?

thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Replication switch Master to slave and back

am 08.07.2009 23:26:24 von Aaron Blew

--0015175cf73c2eb099046e3864fc
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

The simplest method may be to set them up in a multi-master configuration,
similar to what's documented here:
http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql -replication.html

This way you won't have to worry about switching between two masters in a
failover scenario since they're both writable.

-Aaron

On Wed, Jul 8, 2009 at 1:38 PM, Cantwell, Bryan wrote:

> I have successfully set up a replication master/slave scenario with my
> MySQL 5.0.51a
> Now in the event of a fail over to the slave (an identical appliance), I
> want the old master to become the slave for an eventual switch back the
> other way.
> Would it really take the same process to keep the old master up to date
> with the new one? Or is there a way to bring it up to date with the new
> machine without a mysqldump or copying data files?
>
> I have binary logging running on both machines in hopes that I could just
> tell the new slave how to catch up with the new master...
> Any assistance here?
>
> thanks
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=aaronblew@gmail.com
>
>

--0015175cf73c2eb099046e3864fc--

Re: Replication switch Master to slave and back

am 08.07.2009 23:35:55 von Marcus Bointon

--Apple-Mail-24-346299441
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

On 8 Jul 2009, at 22:26, Aaron Blew wrote:

> The simplest method may be to set them up in a multi-master
> configuration,
> similar to what's documented here:
> http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql -replication.html
>
> This way you won't have to worry about switching between two masters
> in a
> failover scenario since they're both writable.


That setup works very well for me, but I also use google's mmm on top
of it so that the switch is transparent to client apps. Works
beautifully.

https://launchpad.net/mysql-mmm

Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info@hand CRM solutions
marcus@synchromedia.co.uk | http://www.synchromedia.co.uk/



--Apple-Mail-24-346299441
Content-Disposition: attachment;
filename=smime.p7s
Content-Type: application/pkcs7-signature;
name=smime.p7s
Content-Transfer-Encoding: base64

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIILHzCCBN0w
ggPFoAMCAQICEHGS++YZX6xNEoV0cTSiGKcwDQYJKoZIhvcNAQEFBQAwezEL MAkGA1UEBhMCR0Ix
GzAZBgNVBAgMEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBwwHU2FsZm9y ZDEaMBgGA1UECgwR
Q29tb2RvIENBIExpbWl0ZWQxITAfBgNVBAMMGEFBQSBDZXJ0aWZpY2F0ZSBT ZXJ2aWNlczAeFw0w
NDAxMDEwMDAwMDBaFw0yODEyMzEyMzU5NTlaMIGuMQswCQYDVQQGEwJVUzEL MAkGA1UECBMCVVQx
FzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRS VVNUIE5ldHdvcmsx
ITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMt VVROLVVTRVJGaXJz
dC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMIIBIjANBgkqhkiG 9w0BAQEFAAOCAQ8A
MIIBCgKCAQEAsjmFpPJ9q0E7YkY3rs3BYHW8OWX5ShpHornMSMxqmNVNNRm5 pELlzkniii8efNIx
B8dOtINknS4p1aJkxIW9hVE1eaROaJB7HHqkkqgX8pgV8pPMyaQylbsMTzC9 mKALi+VuG6JG+ni8
om+rWV6lL8/K2m2qL+usobNqqrcuZzWLeeEeaYji5kbNoKXqvgvOdjp6Dpvq /NonWz1zHyLmSGHG
TPNpsaguG7bUMSAsvIKKjqQOpdeJQ/wWWq8dcdcRWdq6hw2v+vPhwvCkxWeM 1tZUOt4KpLoDd7Nl
yP0e03RiqhjKaJMeoYV+9Udly/hNVyh00jT/MLbu9mIwFIws6wIDAQABo4IB JzCCASMwHwYDVR0j
BBgwFoAUoBEKIz6W8Qfs4q8p74Klf9AwpLQwHQYDVR0OBBYEFImCZ33EnSZw AEu0UEh83j2uBG59
MA4GA1UdDwEB/wQEAwIBBjAPBgNVHRMBAf8EBTADAQH/MB0GA1UdJQQWMBQG CCsGAQUFBwMCBggr
BgEFBQcDBDARBgNVHSAECjAIMAYGBFUdIAAwewYDVR0fBHQwcjA4oDagNIYy aHR0cDovL2NybC5j
b21vZG9jYS5jb20vQUFBQ2VydGlmaWNhdGVTZXJ2aWNlcy5jcmwwNqA0oDKG MGh0dHA6Ly9jcmwu
Y29tb2RvLm5ldC9BQUFDZXJ0aWZpY2F0ZVNlcnZpY2VzLmNybDARBglghkgB hvhCAQEEBAMCAQYw
DQYJKoZIhvcNAQEFBQADggEBAJ2Vyzy4fqUJxB6/C8LHdo45PJTGEKpPDMng q4RdiVTgZTvzbRx8
NywlVF+WIfw3hJGdFdwUT4HPVB1rbEVgxy35l1FM+WbKPKCCjKbI8OLp1Er5 7D9Wyd12jMOCAU9s
APMeGmF0BEcDqcZAV5G8ZSLFJ2dPV9tkWtmNH7qGL/QGrpxp7en0zykX2OBK nxogL5dMUbtGB8SK
N04g4wkxaMeexIud6H4RvDJoEJYRmETYKlFgTYjrdDrfQwYyyDlWjDoRUtNB pEMD9O3vMyfbOeAU
TibJ2PU54om4k123KSZB6rObroP8d3XK6Mq1/uJlSmM+RMTQw16Hc6mYHK9/ FX8wggY6MIIFIqAD
AgECAhBx3xrKwDO5B7CQiO3prEe8MA0GCSqGSIb3DQEBBQUAMIGuMQswCQYD VQQGEwJVUzELMAkG
A1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVU aGUgVVNFUlRSVVNU
IE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2 MDQGA1UEAxMtVVRO
LVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMB4X DTA4MTEyODAwMDAw
MFoXDTA5MTEyODIzNTk1OVowgeMxNTAzBgNVBAsTLENvbW9kbyBUcnVzdCBO ZXR3b3JrIC0gUEVS
U09OQSBOT1QgVkFMSURBVEVEMUYwRAYDVQQLEz1UZXJtcyBhbmQgQ29uZGl0 aW9ucyBvZiB1c2U6
IGh0dHA6Ly93d3cuY29tb2RvLm5ldC9yZXBvc2l0b3J5MR8wHQYDVQQLExYo YykyMDAzIENvbW9k
byBMaW1pdGVkMRcwFQYDVQQDEw5NYXJjdXMgQm9pbnRvbjEoMCYGCSqGSIb3 DQEJARYZbWFyY3Vz
QHN5bmNocm9tZWRpYS5jby51azCCASIwDQYJKoZIhvcNAQEBBQADggEPADCC AQoCggEBAJq12Fwn
mDc8j/J+fsyJFdXkxSsbex2n5eXKm0Tjwy+IPE2F5jxpaITqju9WtxMcsjtt P+CZfAdOQQD/R1fg
jPQLXDifxudsMogyOmX2HdoEpGFITEnes21U+hKTU87DlIM+hEZViuOqS8Ym Xj8oNISFYAa90lvs
nt7jtu/eyDdvrZJ0q6QnntRUaeSsz8ngV8Lqshmo/1qmz67JL77MfJgkSfEP 6fJkqb/e9kKjJ1Tn
HipP2PmZTQUnHwcOs/0uaSgOumgpgMyX8fiQ33VV/DGMcTl9P0oLB0GopAKw qlHoXPCJXFkX6LLi
Mm9bdoKUIW0kwnRx1HWjvDrih9UeMmECAwEAAaOCAhswggIXMB8GA1UdIwQY MBaAFImCZ33EnSZw
AEu0UEh83j2uBG59MB0GA1UdDgQWBBSiqul8UOEn/VVzRp+EXywCAx973TAO BgNVHQ8BAf8EBAMC
BaAwDAYDVR0TAQH/BAIwADAgBgNVHSUEGTAXBggrBgEFBQcDBAYLKwYBBAGy MQEDBQIwEQYJYIZI
AYb4QgEBBAQDAgUgMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQECAQEBMCswKQYI KwYBBQUHAgEWHWh0
dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMIGlBgNVHR8EgZ0wgZowTKBK oEiGRmh0dHA6Ly9j
cmwuY29tb2RvY2EuY29tL1VUTi1VU0VSRmlyc3QtQ2xpZW50QXV0aGVudGlj YXRpb25hbmRFbWFp
bC5jcmwwSqBIoEaGRGh0dHA6Ly9jcmwuY29tb2RvLm5ldC9VVE4tVVNFUkZp cnN0LUNsaWVudEF1
dGhlbnRpY2F0aW9uYW5kRW1haWwuY3JsMGwGCCsGAQUFBwEBBGAwXjA2Bggr BgEFBQcwAoYqaHR0
cDovL2NydC5jb21vZG9jYS5jb20vVVROQUFBQ2xpZW50Q0EuY3J0MCQGCCsG AQUFBzABhhhodHRw
Oi8vb2NzcC5jb21vZG9jYS5jb20wJAYDVR0RBB0wG4EZbWFyY3VzQHN5bmNo cm9tZWRpYS5jby51
azANBgkqhkiG9w0BAQUFAAOCAQEAKfnMTtYONyd2o63aCpSJs8GpopYaEU3k sEBgOAfTFe52yx+k
6eLyah2I0N+lnBmDdLDeR7Eel6zWjKZmaNW9r1h7lRkuzLt6VqpjbBPefOk3 UWkjwJ8j0opKrFSW
UXg+fghnuNVptw3STOyWQKk+fEKtiuf+Ygpnl+QnJWsVjSeasklYqLImXYmn IM3N4y/18UQFztXD
+Z+cn7ZLnBXEfSb/GatxP9JYuhLRTV1YMwtcVhGydHvJTzug+5+wBPdlI8L1 5YecgOR4JxoNJ5T9
Odt0TMPxxVi+m2pZw6Ni3fh8yeKoLsBreKTAX3IsKGsIPJzczQvnVeRrTLaH is9r2DGCA/wwggP4
AgEBMIHDMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcT DlNhbHQgTGFrZSBD
aXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsT GGh0dHA6Ly93d3cu
dXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQg QXV0aGVudGljYXRp
b24gYW5kIEVtYWlsAhBx3xrKwDO5B7CQiO3prEe8MAkGBSsOAwIaBQCgggIN MBgGCSqGSIb3DQEJ
AzELBgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTA5MDcwODIxMzU1NVow IwYJKoZIhvcNAQkE
MRYEFDbg1n/1ffgB+VFV+JVHv6x7cmHCMIHUBgkrBgEEAYI3EAQxgcYwgcMw ga4xCzAJBgNVBAYT
AlVTMQswCQYDVQQIEwJVVDEXMBUGA1UEBxMOU2FsdCBMYWtlIENpdHkxHjAc BgNVBAoTFVRoZSBV
U0VSVFJVU1QgTmV0d29yazEhMB8GA1UECxMYaHR0cDovL3d3dy51c2VydHJ1 c3QuY29tMTYwNAYD
VQQDEy1VVE4tVVNFUkZpcnN0LUNsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQg RW1haWwCEHHfGsrA
M7kHsJCI7emsR7wwgdYGCyqGSIb3DQEJEAILMYHGoIHDMIGuMQswCQYDVQQG EwJVUzELMAkGA1UE
CBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUg VVNFUlRSVVNUIE5l
dHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQG A1UEAxMtVVROLVVT
RVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhBx3xrK wDO5B7CQiO3prEe8
MA0GCSqGSIb3DQEBAQUABIIBABKhhebmvuNHETkEkBDUEXUcmCZbhxfJy2Gq dfjqBiMg59IA7TsU
G4dweaoR5K3A059S6FF3BgbufQQXQR0+2oBlExHhx9Kz6lxZOp2VJPGH6xKM 5Sk4k4sgK5OUNvTR
X5xm4XSbr49aS5E37dUDwnafn9448rJh0q/QaiH1O5mWdbPQcweB5qWt7Xdh iLjGlE5AwK6DyVez
uTEd+kRP42Z4Ni4VGlKjCGxLqephKOc4T3TUG4LnAnLOI2ZyIZh2kEka422r 556gYiXE5tXibFpP
vtyz1TBM9Dp9R6ZZ/8o2UdKPvSsI7lgIpZtAIwB7nc/ToCd/ZcKQKoY7eWb9 baEAAAAAAAA=

--Apple-Mail-24-346299441--

RE: COUNT from 2 tables

am 09.07.2009 00:11:33 von Gary Smith

Create a view or sub select, denormalizing the data and then group it.

select month, sum(login) as num_logins, sum(download) as num_downloads
from
(
select=20
monthname(s.created) as month_name
, if(ifnull(s.id, 0) > 0, 1, 0) as login
, if(ifnull(d.id, 0) > 0, 1, 0) as download
from sessions s left join downloads d
on s.id =3D d.session_id
)
group by month name;

which is the left table? downloads or logins? If logins shouldn't it be o=
n the left side of the ON statement? (I'm not sure) My understanding is th=
at is the ON statement that runs the left join, not which table is listed f=
irst (I could be wrong though -- chime in if you know the definitive answer=
please).

Anyway, try this and see if it gets you closer.
________________________________________
From: b [mysql@logi.ca]
Sent: Wednesday, July 08, 2009 12:55 PM
To: mysql@lists.mysql.com
Subject: Re: COUNT from 2 tables

On 07/08/2009 03:33 PM, Gary Smith wrote:
> Off the top of my head, try this.
>
> SELECT
> MONTHNAME(s.created) AS month,
> sum(if(ifnull(s.id,0)> 0, 1, 0)) AS num_logins,
> sim(if(ifnull(d.id, 0)> 0, 1, 0)) AS num_downloads
> FROM sessions AS s LEFT JOIN downloads AS d
> ON d.session_id =3D s.id GROUP BY month
>

Nope, I'm still getting those same incorrect sums. Thanks, though. It
seems to me that the problem is that I'm grouping by the month for one
table but counting from both.

I'd paste the output here but I just upgraded Fedora and the BETA (wtf?)
version of Thunderbird crashes when I paste into an email (how the
earlier paste worked I don't know).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgary@primeexalia.co=
m=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: COUNT from 2 tables

am 09.07.2009 02:44:40 von B

On 07/08/2009 06:11 PM, Gary Smith wrote:
> Create a view or sub select, denormalizing the data and then group it.
>
> select month, sum(login) as num_logins, sum(download) as num_downloads
> from
> (
> select
> monthname(s.created) as month_name
> , if(ifnull(s.id, 0)> 0, 1, 0) as login
> , if(ifnull(d.id, 0)> 0, 1, 0) as download
> from sessions s left join downloads d
> on s.id = d.session_id
> )
> group by month name;
>
> which is the left table? downloads or logins? If logins shouldn't it be on the left side of the ON statement? (I'm not sure) My understanding is that is the ON statement that runs the left join, not which table is listed first (I could be wrong though -- chime in if you know the definitive answer please).
>
> Anyway, try this and see if it gets you closer.

I had to change month_name to month and add "AS foo" just before the
GROUP BY ("Every derived table must have its own alias") but still no
joy. I'm still getting those same incorrect numbers.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org