On Fri, Jan 1, 2016 at 9:31 PM, Peter Brawley
wrote:
> On 1/1/2016 19:24, Larry Martell wrote:
>>
>> On Fri, Jan 1, 2016 at 2:12 PM, Peter Brawley
>> wrote:
>>>
>>> On 12/31/2015 0:51, Larry Martell wrote:
>>>>
>>>> I need to count t
On Fri, Jan 1, 2016 at 2:12 PM, Peter Brawley
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
>> some count when gr
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 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
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)
>
> H
n is: how can i 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
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 `GR
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 who
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"
>
> I haven't used MYISAM in a long time, so i'm not s
Glad you got to the bottom of it Joey.
On 7 Oct 2011 01:23, "Joey L" 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 show full
>
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
> From: Joey L
>
> 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 Havana, there are t
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 w6h8a_sh404sef_ur
rls WHERE newurl =
'index.php?option=com_limos&city=Armo |
| 2615 | p_092211 | localhost | p_092211 | Query |7 | Locked
| SELECT `oldurl`, `newurl`, `dateadd` FROM w6h8a_sh404sef_urls
WHERE `oldurl`='MLF/index.php/' ORDER |
| 2616 | p_092211 | localhost | p_092211 | Query |7 | Locked
| SELECT * FROM w6h8a_sh404sef_urls WHERE `oldurl`='MLF/index.php'
|
| 2617 | p_092211 | localhost |
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
nce through 2 disks and effectively 1 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 y
sks and effectively 1 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 ma
ugh), 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
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:
Joey, does your 'large' table get
On Thu, Oct 6, 2011 at 3:22 PM, Joey L wrote:
> here is mysqlreport ---
>
> root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
> Use of uninitialized value $is in multiplication (*) at ./mysqlreport
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 5.1.49-
> 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
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
ou may need to change.
>>>> #server-id = 1
>>>> #log_bin= /var/log/mysql/mysql-bin.log
>>>> expire_logs_days= 10
>>>> max_binlog_size = 100M
>>>> #binlog_do_db = include_database_nam
e
>>> #
>>> # * InnoDB
>>> #
>>> # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
>>> # Read the manual for more InnoDB related options. There are many!
>>> #
>>> # * Security Features
>>> #
>>> # Read t
DB related options. There are many!
>> #
>> # * Security Features
>> #
>> # Read the manual, too, if you want chroot!
>> # chroot = /var/lib/mysql/
>> #
>> # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
>> #
>&g
but no tab completition
>
> [isamchk]
> key_buffer = 16M
>
> #
> # * IMPORTANT: Additional settings that can override those from this file!
> # The files must end with '.cnf', otherwise they'll be ignored.
> #
> !includedir /etc/mysql/conf
rwise 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 wrote:
> Can you run show processlist in another connection while the select
> count(*) query is running and say what the state column i
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 wrote:
> this is not a real query on the site - it is just a way i am measuring
> performance on mysql - I do not know if
:58 AM, Andrés Tello wrote:
> have you tried
>
> select count(yourindex) instead of select count(*) ?
>
>
> On Mon, Oct 3, 2011 at 7:53 AM, Joey L wrote:
>
>> Thanks for the input -
>> 1. I will wait 48 hours and see what happens.
>> 2. can you tell me what
have you tried
select count(yourindex) instead of select count(*) ?
On Mon, Oct 3, 2011 at 7:53 AM, Joey L 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 ser
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 |
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 siz
t;<" -- 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 ;
>
> +--+
> | count(*) |
> +--+
> | 8908193 |
> +--+
> 1 row in
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 coun
0.0 0:00.04 [migration/0]
>>4 root 20 0 000 S0 0.0 0:00.16 [ksoftirqd/0]
>>
>>
>> On Sun, Oct 2, 2011 at 9:55 AM, Andrew Moore wrote:
>>
>> > Is your table MyISAM or InnoDB?
>> >
>> > A
>> >
>> >
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 t
> On Sun, Oct 2, 2011 at 2:44 PM, 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 in it.
>> I have noticed issues on my web pages so that is
Is your table MyISAM or InnoDB?
A
On Sun, Oct 2, 2011 at 2:44 PM, 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 in it.
> I have noticed issues on my web
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
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-
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 = tea
math.. and the DATE_FORMAT call might not
> even be needed depending on the actual application.
>
> select
> DATE_FORMAT(start_time, "%Y-%m-%d %h:" ) as dhour,
> 10*(minute(start_time)%6) as dtime ,count(*)
> from table
> group by dhour,dtime;
>
&
me)%6) as dtime ,count(*)
from table
group by dhour,dtime;
-Hank
On Wed, Oct 6, 2010 at 4:22 PM, Johan De Meersman 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_u
king ? :-D
On Wed, Oct 6, 2010 at 5:44 PM, Hank 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
>
> >>
> >&g
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
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 y
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
> Hi!
>
> How would one go about to construct a query that counts items within an
> incremen
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 p
>-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
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
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
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
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
On Fri, Jul 9, 2010 at 9:09 PM, Andrés Tello 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 mai
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 t
In infinite wisdom Brent Clark 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 problem.
The main causes
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 A
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,
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.
* Bob Cole [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
>
* Dan Nelson [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(), mysql_fetch_row(), and 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
from the system MySQL 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 c
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
ifang
-Original Message-
From: 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
x27;1561481912', '/Cooking/1561481912.jpg', '3.', '2010-05-12
19:00:17', '2008-10-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_availab
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
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.
Kei
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
_added`),
> KEY `manufacturers_id` (`manufacturers_id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1
>
> So, I'd like to create a report that grouped by products_date_available,
> counts all records before products_date_available with a
> products_quantity>
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,
`p
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 Quantity>0 bef
I'm trying to produce a report that will tell me how many products were
available with a Quantity>0 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 Ma
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
Thanks!
2010/2/16 Peter Brawley
> 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 Menend
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 (
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
mensaje text,
leido tinyint(3) DEFAULT '0' NOT NULL,
PRIMARY KEY (uid),
KEY parent (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 m
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
wrote:
> >-Original M
>-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 d
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 wou
se.
>
> Regards,
> Gavin Towey
>
> -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 qu
l 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
name
How can i list all records from PROGS with a sum o
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. :-
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)>
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.
From: b [my...@logi.ca]
Sent: Wednesday, July 08, 2009 12:55 PM
To: mysql@lists.mysql.com
Subject: Re: COUNT from 2
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
month
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> des
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
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
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
Thanks to everyone who helped me out with this, just what I needed and
this is now working for me.
One further question...
I'm using a subquery as suggested:
SELECT
`Organisations`.`organisation_id`,
`Organisations`.`name`,
(SELECT COUNT(*) FROM `Notes__Organisations`
Nigel,
>I want to select the name and id from "Organisations"
>along with a count of the number of one-to-many
>relationships it has in each of the two tables.
Aggregation multiplies across multiple joins. For suggested solutions
see "Aggregates across
Notes__Organisations" and "Organisations__People".
I want to select the name and id from "Organisations" along with a count
of the number of one-to-many relationships it has in each of the two
tables. I want a count of 0 if there are none, so I'm using two LEFT
JOINs and
= t2.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 ,
>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
You need to group by forum_id...
On 2/19/09 11:09 AM, "Artem Kuchin" 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
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 BY
1 - 100 of 990 matches
Mail list logo
