On Fri, Jan 1, 2016 at 9:31 PM, Peter Brawley
<peter.braw...@earthlink.net> wrote:
> On 1/1/2016 19:24, Larry Martell wrote:
>>
>> On Fri, Jan 1, 2016 at 2:12 PM, Peter Brawley
>> <peter.braw...@earthlink.net> wrote:
>>>
>>> On 12/31/2015 0
On Fri, Jan 1, 2016 at 2:12 PM, Peter Brawley
<peter.braw...@earthlink.net> wrote:
> On 12/31/2015 0:51, Larry Martell wrote:
>>
>> I need to count the number of rows in a table that are grouped by a
>> list of columns, but I also need to exclude rows that have more then
On 12/31/2015 0:51, Larry Martell wrote:
I need to count the number of rows in a table that are grouped by a
list of columns, but I also need to exclude rows that have more then
some count when grouped by a different set of columns. Conceptually,
this is not hard, but I am having trouble doing
I need to count the number of rows in a table that are grouped by a
list of columns, but I also need to exclude rows that have more then
some count when grouped by a different set of columns. Conceptually,
this is not hard, but I am having trouble doing this efficiently.
My first counting query
On Tue, Sep 25, 2012 at 12:31 AM, Rick James rja...@yahoo-inc.com wrote:
SET foo (...)
Maybe:
SELECT BIT_COUNT(foo) ...
It works. Thanks! :)
Greetings,
Marco
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
count that items ( value1 till value5) ?
Greetings,
Marco
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
SET foo (...)
Maybe:
SELECT BIT_COUNT(foo) ...
-Original Message-
From: Morning Star [mailto:morning.star.c...@gmail.com]
Sent: Monday, September 24, 2012 7:02 AM
To: mysql@lists.mysql.com
Subject: [ask] count items in the SET(value1,value2,value3)
Hi guys,
i created a table
Hi, I'm trying to get this work;
SELECT distinct `term`,count(*) as count FROM
blp_sql_distinct_temp_table where count = 5 group by `term` order by
count DESC
But I get this error;
Unknown column 'count' in 'where clause'
How do I get only those records whose group by count is above 5
Hi,
you have to use `HAVING' instead of `WHERE' like this.
SELECT DISTINCT
`term`,
COUNT(*) AS count
FROM blp_sql_distinct_temp_table
GROUP BY `term`
HAVING count = 5
ORDER BY count DESC;
put `HAVING' next of `GROUP BY'.
`WHERE' behaves at before aggregate of `GROUP BY'.
your SQL means like
Good to see the issue has been solved. What I noticed in the mysqltuner output,
is that you may want to enlarge your table_cache and open files limit before
you run into problems there.
- Original Message -
From: Johnny Withers joh...@pixelated.net
I haven't used MYISAM in a long
/
any thoughts or help would be appricated.
thanks
On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen eric.ber...@gmail.com wrote:
Can you run show processlist in another connection while the select
count(*) query is running and say what the state column is?
On Mon, Oct 3, 2011 at 7:00 AM, Joey L mjh2
/
any thoughts or help would be appricated.
thanks
On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen eric.ber...@gmail.com wrote:
Can you run show processlist in another connection while the select
count(*) query is running and say what the state column is?
On Mon, Oct 3, 2011 at 7:00 AM, Joey L mjh2
with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
any thoughts or help would be appricated.
thanks
On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen eric.ber...@gmail.com wrote:
Can you run show processlist in another connection while the select
count(*) query is running
be ignored.
#
!includedir /etc/mysql/conf.d/
any thoughts or help would be appricated.
thanks
On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen eric.ber...@gmail.com wrote:
Can you run show processlist in another connection while the select
count(*) query is running and say what the state column
...@gmail.com wrote:
Can you run show processlist in another connection while the select
count(*) query is running and say what the state column is?
On Mon, Oct 3, 2011 at 7:00 AM, Joey L mjh2...@gmail.com wrote:
this is not a real query on the site - it is just a way i am measuring
performance on mysql
I keep finding it extremely peculiar that a count(*) on a MyISAM table would
take that long. InnoDB needs to effectively *count* the records, but MyISAM
keeps accurate statistics and can just read it from the metadata.
This suggests to me that not all your metadata (ie., table descriptors et al
thanks for the response - but do not believe queries are the issue
because - Like I said - i have other websites doing the same exact
queries as I am doing on the site with the 9gig table.
Contrary to popular believe, size DOES matter... And having a table large
enough so it doesn't fit in
here is mysqlreport ---
root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
Use of uninitialized value $is in multiplication (*) at ./mysqlreport line 829.
Use of uninitialized value in formline at ./mysqlreport line 1227.
MySQL
Joey, does your 'large' table get
On Thu, Oct 6, 2011 at 3:22 PM, Joey L mjh2...@gmail.com wrote:
here is mysqlreport ---
root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
Use of uninitialized value $is in multiplication (*) at
concurrency mixed workloads such as yours, it will cause
locking and maybe thats why your count has such a delay. Such activity may
be better suited to InnoDB engine (you must configure and tune for this, not
JUST change the engine).
HTH
Andy
On Thu, Oct 6, 2011 at 5:05 PM, Andrew Moore eroomy
if in a mirrored
configuration. The stats show that you're configured for MyISAM and that
you're tables are taking reads and writes (read heavy though), MyISAM
doesn't like high concurrency mixed workloads such as yours, it will cause
locking and maybe thats why your count has such a delay. Such activity may
configuration. The stats show that you're configured for MyISAM and that
you're tables are taking reads and writes (read heavy though), MyISAM
doesn't like high concurrency mixed workloads such as yours, it will
cause
locking and maybe thats why your count has such a delay. Such activity
may
Precisely my point Singer. There's a workload here that isn't friendly with
table level locking and I would hazard a guess that there's some fights over
IO due to load vs resources. The count is going to be queued as you
describe.
A
On Thu, Oct 6, 2011 at 6:09 PM, Singer X.J. Wang w
Moore eroomy...@gmail.com wrote:
Precisely my point Singer. There's a workload here that isn't friendly with
table level locking and I would hazard a guess that there's some fights over
IO due to load vs resources. The count is going to be queued as you
describe.
A
On Thu, Oct 6, 2011 at 6:09
I've sent this email a few times now, mysql list kept rejecting it due to
size, sorry for any duplicates
I think you need to examine this query in particular:
| 2567 | p_092211 | localhost | p_092211 | Query | 11 | Sending
data | select oldurl, newurl, id, dateadd from
From: Joey L mjh2...@gmail.com
i did google search - myisam is faster...i am not really doing any
transaction stuff.
That's true for read-only. But if you have a mix of reads and writes, MYISAM
locks tables during writes, which could be blocking reads.
In a museum in
Guys - I wanted to thank you all very much for your help
I found the offending code on the website !
thank you very very very much...
what did it for me was a combination of show processlist and show full
processlist.
I saw the full queries and the main thing was that it was doing a
query
Glad you got to the bottom of it Joey.
On 7 Oct 2011 01:23, Joey L mjh2...@gmail.com wrote:
Guys - I wanted to thank you all very much for your help
I found the offending code on the website !
thank you very very very much...
what did it for me was a combination of show processlist and
Thanks for the input -
1. I will wait 48 hours and see what happens.
2. can you tell me what are some performance tests I can do to help me
better tune my server ?
3. I am concerned about this table : | w6h8a_sh404sef_urls |
MyISAM | 10 | Dynamic| 8908402 |174
have you tried
select count(yourindex) instead of select count(*) ?
On Mon, Oct 3, 2011 at 7:53 AM, Joey L mjh2...@gmail.com wrote:
Thanks for the input -
1. I will wait 48 hours and see what happens.
2. can you tell me what are some performance tests I can do to help me
better tune my
:58 AM, Andrés Tello mr.crip...@gmail.com wrote:
have you tried
select count(yourindex) instead of select count(*) ?
On Mon, Oct 3, 2011 at 7:53 AM, Joey L mjh2...@gmail.com wrote:
Thanks for the input -
1. I will wait 48 hours and see what happens.
2. can you tell me what are some
Can you run show processlist in another connection while the select
count(*) query is running and say what the state column is?
On Mon, Oct 3, 2011 at 7:00 AM, Joey L mjh2...@gmail.com wrote:
this is not a real query on the site - it is just a way i am measuring
performance on mysql - I do
I have having issues with mysql db - I am doing a select count(*) from
table -- and it take 3 to 4 min.
My table has about 9,000,000 records in it.
I have noticed issues on my web pages so that is why i did this test.
I have about 4 gig of memory on the server.
Is there anything I can do to fix
Is your table MyISAM or InnoDB?
A
On Sun, Oct 2, 2011 at 2:44 PM, Joey L mjh2...@gmail.com wrote:
I have having issues with mysql db - I am doing a select count(*) from
table -- and it take 3 to 4 min.
My table has about 9,000,000 records in it.
I have noticed issues on my web pages so
?
A
On Sun, Oct 2, 2011 at 2:44 PM, Joey L mjh2...@gmail.com wrote:
I have having issues with mysql db - I am doing a select count(*) from
table -- and it take 3 to 4 min.
My table has about 9,000,000 records in it.
I have noticed issues on my web pages so that is why i did this test.
I have about 4
I'd suggest mysqltuner. You can get it by using:
wget http://mysqltuner.pl
See what suggestions that makes
On 10/02/2011 06:44 AM, Joey L wrote:
I have having issues with mysql db - I am doing a select count(*) from
table -- and it take 3 to 4 min.
My table has about 9,000,000 records
L mjh2...@gmail.com wrote:
I have having issues with mysql db - I am doing a select count(*) from
table -- and it take 3 to 4 min.
My table has about 9,000,000 records in it.
I have noticed issues on my web pages so that is why i did this test.
I have about 4 gig of memory on the server
The section called: Variables to adjust: --when it says -- does this
mean I have to set it higher in my.cnf file ?? and if I have a -- does
this mean I have to set it lower ??
thanks...here is the info below you both asked for :
mysql select count(*) from w6h8a_sh404sef_urls
this mean I have to set it lower ??
thanks...here is the info below you both asked for :
mysql select count(*) from w6h8a_sh404sef_urls ;
+--+
| count(*) |
+--+
| 8908193 |
+--+
1 row in set (2 min 5.53 sec)
| w6h8a_session | MyISAM | 10
The meaning is:
increase max_connections
reduce wait_timeout
-- 28800 is wait 8 hours before closing out dead connections
same for interactive_timeout
increase key_buffer_size ( 7.8G) increase join_buffer_size
-- This keeps mysql from having to run to disk constantly for keys
-- Key buffer
Anyone has idea if this is normal?
I have a rather hi count on some mutexes on one of my servers:
Find below:
- server status
- last section of mutex status
- full innodb status.
Thanks
Claudio
mysql status;
--
mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu
Hi,
I've the following query
SELECT COUNT(players_id) AS players_count
FROM players
WHERE teams_id 0
GROUP BY teams_id
ORDER BY players_count DESC
However, I've another field called original_teams_id and want to include the
COUNT with players_count, when original_teams_id = teams_id
Cheers
)%6) as dtime ,count(*)
from table
group by dhour,dtime;
-Hank
On Wed, Oct 6, 2010 at 4:22 PM, Johan De Meersman vegiv...@tuxera.be
wrote:
Two people already who suggested a text-based approach vs. my numeric
approach.
Analysing, my method takes a single function call per record
Hi!
How would one go about to construct a query that counts items within an
increment or span of time, let's say increments of 10 minutes?
Imagine a simple table where each row has a timestamp, and the query
should return the count of items occurring within the timespan of a
defined period
convert to unixtime, convert your interval to unixtime, creatively combine
with integer division to get a base number for each period, group by that
and count().
2010/10/6 Pascual Strømsnæs pasc...@egoria.no
Hi!
How would one go about to construct a query that counts items within
Maybe you could use something like the following to truncate your times to
10 minute increments before doing your GROUP BY and COUNT():
select concat(date_format(timestamp_col, '%Y-%m-%d %H:'),
truncate(minute(timestamp_col) / 10, 0), '0') from your_table;
-Travis
Here's what I came up with:
select concat(left(DATE_FORMAT(start_time, %Y-%m-%d %h:%i
),15),0) as time, count(*) from table group by time
-Hank
How would one go about to construct a query that counts items within an
increment or span of time, let's say increments of 10 minutes?
Imagine
On Wed, Oct 6, 2010 at 5:44 PM, Hank hes...@gmail.com wrote:
Here's what I came up with:
select concat(left(DATE_FORMAT(start_time, %Y-%m-%d %h:%i
),15),0) as time, count(*) from table group by time
-Hank
How would one go about to construct a query that counts items within
,count(*)
from table
group by dhour,dtime;
-Hank
On Wed, Oct 6, 2010 at 4:22 PM, Johan De Meersman vegiv...@tuxera.be wrote:
Two people already who suggested a text-based approach vs. my numeric
approach.
Analysing, my method takes a single function call per record (to_unixtime);
Travis
On Fri, Sep 17, 2010 at 3:51 AM, Shawn Green (MySQL)
shawn.l.gr...@oracle.com wrote:
So if 10 rows of A match your conditions, 1 row from B match your
conditions, and 10 rows from C match your conditions, then this query
produces 10*1*10 total row combinations.
Umm. It's friday, so I may
-Original Message-
From: Shawn Green (MySQL) [mailto:shawn.l.gr...@oracle.com]
Sent: Thursday, September 16, 2010 9:51 PM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: Re: Update record count
On 9/16/2010 5:12 PM, Jerry Schwartz wrote:
I should be able to figure this out, but I'm
I should be able to figure this out, but I'm puzzled. Here's a simplified
example:
UPDATE a JOIN b ON a.kb = b.kb JOIN c ON b.kc = c.kc
SET a.f1 = NOW(),
b.f2 = NOW()
WHERE c.f3 IN ('x', 'y', 'z')
AND b.f4 = 'yen';
It seems to me that if there are 3 rows found in `c` that match a total of 10
On 9/16/2010 5:12 PM, Jerry Schwartz wrote:
I should be able to figure this out, but I'm puzzled. Here's a simplified
example:
UPDATE a JOIN b ON a.kb = b.kb JOIN c ON b.kc = c.kc
SET a.f1 = NOW(),
b.f2 = NOW()
WHERE c.f3 IN ('x', 'y', 'z')
AND b.f4 = 'yen';
It seems to me that if there are
On Fri, Jul 9, 2010 at 9:09 PM, Andrés Tello mr.crip...@gmail.com wrote:
Hi, I'm using mysql 4.1.21, a legacy system. Next step to migrate it, but
for now, I need the community help.
I have a 23GB table, if I do a select count(over_an_index) from table it
uses 1.4minutes to read. The main
As usual, after you send a mail, you check for other things
From the 32GB of ram, I only was allocating 2G, not by the process, but by
all the S.O...
free -G reported barely 1 gig of ram...
XD - happy face that I have lots of ram...
now my face is like: ¬¬ - why I didn't verfy the amount of
Hi, I'm using mysql 4.1.21, a legacy system. Next step to migrate it, but
for now, I need the community help.
I have a 23GB table, if I do a select count(over_an_index) from table it
uses 1.4minutes to read. The main issue is that this table is the main table
of a system and each query is taking
In infinite wisdom Brent Clark brentgclarkl...@gmail.com wrote:
All servers are in datacentres. Im not 100% sure if its latency related,
but I see a load or SLEEP when I do mysqladin proc, which appears to be
hogging mysql.
My question is. Would adjusting timeout values address this
with single line of three columns, each with a count of the number
of interviews for that reporting period:
|| Under 18 ||19-65 || over 65 ||
||5|| 19|| 23 ||
I've made three queries to select the counts for each age range
Chris,
You are using Count when you should be using Sum. Here is a solution
you can try:
SELECT
SUM(IF(DATEDIFF(dateofinterview,dateofbirth)/365.25 BETWEEN 0 AND
18.999, 1,0)) AS 18 and Under,
SUM(IF(DATEDIFF(dateofinterview,dateofbirth)/365.25 BETWEEN 19 AND
65.999
Hiya
I have a server that gets connections / requests from Germany and South
Africa, Johannesburg (my server's based in Cape Town).
All servers are in datacentres. Im not 100% sure if its latency related,
but I see a load or SLEEP when I do mysqladin proc, which appears to be
hogging mysql.
You might get closer to what you want if you put your command in a text file
and run it from the command line.
On a Mac OS X, I put a similar command:
select count(*) from testTable;
into a small text file:
testCount.txt
and ran this command from the Terminal:
mysql -u username
* Dan Nelson dnel...@allantgroup.com [100514 21:38]:
You can't do it with one function call, but you can do it, since the MySQL
cli was able to print 16 in your example above, and it was written in C.
Take a look at mysql_store_result(), mysql_num_fields(),
mysql_field_count(),
* Bob Cole bobc...@earthlink.net [100515 06:58]:
You might get closer to what you want if you put your command in a text file
and run it from the command line.
On a Mac OS X, I put a similar command:
select count(*) from testTable;
into a small text file:
testCount.txt
and ran
I have MySQL version 5.0.84 on linux slackware 13.0 32-bit.
I am working with a relatively new API written in a programming
language with a small user base (newlisp). The newlisp API imports a
number of C API functions from the system MySQL shared object.
If I were to issue a count(*) query
shared object.
If I were to issue a count(*) query from my monitor interface:
Example:
mysql select count(*) from clients;
+--+
| count(*) |
+--+
| 16 |
+--+
If select count(*) from clients is issued from the newlisp API, is
there a a C API function
-01 00:00:00', '2008-10-01 00:00:00', '0.50', 1, 1,
7428, 0, '1',
Here is the query I ran:
SELECT date(products_date_available) as Date,
COUNT(products_quantity) as 'Titles Available',
SUM(products_quantity) as 'Books Available'
FROM products
WHERE products_quantity 0
GROUP BY date
: Keith Clark [mailto:keithcl...@k-wbookworm.com]
Sent: 13 May 2010 14:11
To: mysql@lists.mysql.com
Subject: Re: Count Query question
Bob,
Here are a few rows of my data:
17462, 0, '0929998596', '/GraphicNovels/0929998596.jpg', '8.5000',
'2010-05-12 19:02:47', '2008-10-01 00:00:00', '2008-10-01 00:00
I'm trying to produce a report that will tell me how many products were
available with a Quantity0 before a certain date, and have that ordered
by date.
Table:
Date
Quantity
Result desired
DateQuantity Available
May 1 5000
May 2 5050
May 3 5075
Thanks,
Keith
--
MySQL General
With out the table definitions, I'm not sure how anyone could help. Can
you send the output of show create table for each of the tables
involved in this query?
Chris W
Keith Clark wrote:
I'm trying to produce a report that will tell me how many products were
available with a Quantity0
Chris,
Here is my full table definition:
CREATE TABLE `products` (
`products_id` int(15) NOT NULL AUTO_INCREMENT,
`products_quantity` int(4) NOT NULL,
`products_model` varchar(15) NOT NULL DEFAULT '',
`products_image` varchar(64) DEFAULT NULL,
`products_price` decimal(15,4) DEFAULT NULL,
by products_date_available,
counts all records before products_date_available with a
products_quantity0.
I don't think I'm asking this question properly.
For every date in products_date_available in the table, I'd like to know
the count of items available with products_quantity0 up until
Keith:
Does this work?
SELECT products_date_available, COUNT(products_quantity)
FROM products
WHERE products_quantity 0
GROUP BY products_date_available
Hope this helps,
Bob
On May 12, 2010, at 3:06 PM, Keith Clark wrote:
On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote
Hi Bob,
No, actually it does not. I'm looking for the count of items. From
your query example I only get two rows. This table has over 2 1/2 years
of daily sales data.
Maybe I'm not stating my question correctly...h
Thanks for responding though, greatly appreciated.
Keith
On Wed
0
33 2010-05-08 3
33 2010-05-09 3
33 2010-05-10 0
33 2010-05-11 3
33 2010-05-12 3
and used the following query:
SELECT products_date_available, COUNT(products_quantity),
SUM(products_quantity)
FROM products
WHERE products_quantity
Thanks!
2010/2/16 Peter Brawley peter.braw...@earthlink.net
David,
I need count the messages don'tread in a thread.
Have a look at the edge list examples at
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.
PB
-
David Arroyo Menendez wrote:
Hello,
I've
Hi All,I want to find out number of lines are there in
all stored procedure written.Is it possible to get the
number of lines using a SQL query.Thanks in advance.
--
Regards,
Manasi Save
(pid)
);
Where padre is the id of the parent message. I need count the messages don't
read in a thread. How can I do it?
With
$query=select count(*) as num from tx_cc20_mensajes msj where hidden=0 and
deleted=0 and leido=0 and destinatario=.$uid. and remitente.$uid. and
(padre=.$est_row['uid
Menendez [mailto:david.arr...@bbvaglobalnet.com]
Sent: Tuesday, February 16, 2010 8:27 AM
To: mysql@lists.mysql.com
Subject: count children nodes
Hello,
I've the next table structure:
CREATE TABLE tx_cc20_mensajes (
uid int(11) NOT NULL auto_increment,
pid int(11) DEFAULT '0' NOT NULL
David,
I need count the messages don'tread in a thread.
Have a look at the edge list examples at
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.
PB
-
David Arroyo Menendez wrote:
Hello,
I've the next table structure:
CREATE TABLE tx_cc20_mensajes (
uid int
-Original Message-
From: Miguel Vaz [mailto:pagong...@gmail.com]
Sent: Tuesday, December 15, 2009 10:43 AM
To: mysql@lists.mysql.com
Subject: Count records in join
Hi,
I am stuck with a suposedly simple query:
- i have two tables (:
PROGS
id_prog
name
EVENTS
id
id_prog
Thanks all for the feedback. Here's what i did:
select p.id_prog,count(r.id_event) e from programas p left join(events r)
on(p.id_prog=r.id_prog) group by r.id_event
This gives me a list of all the distinct progs with a count of how many
events on each. I then delete the empty ones.
It would
-Original Message-
From: Miguel Vaz [mailto:pagong...@gmail.com]
Sent: Wednesday, December 16, 2009 9:39 AM
To: Johan De Meersman
Cc: Gavin Towey; mysql@lists.mysql.com
Subject: Re: Count records in join
Thanks all for the feedback. Here's what i did:
select p.id_prog,count(r.id_event) e
Yes, that would do what you mentioned, show all programs with a count on
events, but i need the opposite, show (and delete) all that dont have any
events. Well, just have to use IS NULL instead. Thanks.
MV
On Wed, Dec 16, 2009 at 3:17 PM, Jerry Schwartz
jschwa...@the-infoshop.comwrote
Hi,
I am stuck with a suposedly simple query:
- i have two tables (:
PROGS
id_prog
name
EVENTS
id
id_prog
name
How can i list all records from PROGS with a sum of how many events each
have? I want to find the progs that are empty.
I remember something about using NULL, but i cant remember.
[mailto:pagong...@gmail.com]
Sent: Tuesday, December 15, 2009 10:43 AM
To: mysql@lists.mysql.com
Subject: Count records in join
Hi,
I am stuck with a suposedly simple query:
- i have two tables (:
PROGS
id_prog
name
EVENTS
id
id_prog
name
How can i list all records from PROGS with a sum of how
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
From: b [my...@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
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
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 [my...@logi.ca]
Sent: Wednesday, July 08, 2009 12:55 PM
To: mysql@lists.mysql.com
Subject: Re: COUNT from 2 tables
On 07/08
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)
Hi experts,
Something strange here, use root and mysql (root and mysql are user
accounts) to select count(*) from same table, but the result is different.
login as root:
mysql -uroot -p information_schema
mysql select * from tables where table_name='tb_staff'\G
*** 1
Check your rights ;)
-Ursprüngliche Nachricht-
Von: jinava [mailto:jin...@gmail.com]
Gesendet: Mittwoch, 1. Juli 2009 14:52
An: mysql@lists.mysql.com
Betreff: Re: why different users get different count(*) from same table?
Hi experts,
Something strange here, use root and mysql (root
Hi experts,
something strange here, use root and mysql (root and mysql are user
accounts) to select count(*) from same table, but the result is different.
login as root:
mysql -uroot -p information_schema
mysql select * from tables where table_name='tb_staff'\G
*** 1
I have two simple tables. One - list of forums, second - list of
messages and i want to
get the list of forums with number of messages in each.
Here is the query:
SELECT forums.id , COUNT( forum_msg.id ) AS cnt
FROM forums
LEFT JOIN forum_msg ON forums.id = forum_msg.forum_id
ORDER
You need to group by forum_id...
On 2/19/09 11:09 AM, Artem Kuchin mat...@itlegion.ru wrote:
I have two simple tables. One - list of forums, second - list of
messages and i want to
get the list of forums with number of messages in each.
Here is the query:
SELECT forums.id , COUNT
SELECT forums.id , COUNT( forum_msg.id ) AS cnt
FROM forums
LEFT JOIN forum_msg ON forums.id = forum_msg.forum_id
ORDER BY forums.sorder ASC
Missing GROUP BY.
PB
-
Artem Kuchin wrote:
I have two simple tables. One - list of forums, second - list of
messages and i want to
get the list
.a);
+--+--+--+--+
| a| b| a| c|
+--+--+--+--+
|1 | x| NULL | NULL |
|2 | y|2 | z|
+--+--+--+--+
(Row1 is included but 3 is leftout because it doesnt have a corresponding row
in t1)
Your query
SELECT forums.id , COUNT
On Thu, Oct 23, 2008 at 10:31 PM, Fayland Lam [EMAIL PROTECTED] wrote:
B one SQLs with some operation
SELECT col FROM table WHERE $where
while $count is scalar @cols and real cols is splice(@cols, $start, $rows)
If you're talking about Perl/DBI, doing that normally loads the entire
result set
I have a Char column with text data like ab:cdef:h and I'd like to count
the number of : in the column. I can't find an easy way to do it. I
thought there should be a MySQL function to do this. Any suggestions?
TIA
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com
I have a Char column with text data like ab:cdef:h and I'd like to
count the number of : in the column. I can't find an easy way to do
it. I thought there should be a MySQL function to do this. Any suggestions?
select length('aa:bb:cc:dd')-length(replace('aa:bb:cc:dd',':',''));
btw ever
1 - 100 of 952 matches
Mail list logo