Well ...
I don't know enough about mysql but it must allow you somehow to specify the
column by number as in Oracle and other DBs
SQL select count(*) from product;
COUNT(*)
--
10
SQL select count(1) from product;
COUNT(1)
--
10
Once you just want
Actually, as far as performance goes, it depends on table types. Most of
the MySQL table handlers keep an internal count of record numbers. Thus, a
select count(*) from table; is as fast as it gets.
- Original Message -
From: Siomara Pantarotto [EMAIL PROTECTED]
To: [EMAIL PROTECTED
Cool but how about if you put a where clause to your select??
SQL select count(1) from product where productid 3;
COUNT(1)
--
7
Would count(*) show the same performance as count(1)???
Siomara
From: Eric Fitzgerald [EMAIL PROTECTED]
To: Siomara Pantarotto [EMAIL
|
| 10 | Decription 10 |
+-+---+
10 rows in set (0.03 sec)
mysql SELECT COUNT(*) FROM testcount;
+--+
| COUNT(*) |
+--+
| 10 |
+--+
1 row in set (0.00 sec)
mysql SELECT COUNT(1) FROM testcount;
+--+
| COUNT(1) |
+--+
| 10
That's really cool
Thanks
siomara
From: Eric Fitzgerald [EMAIL PROTECTED]
To: Siomara Pantarotto [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: count(*) on different tables
Date: Mon, 21 May 2001 14:08:00 -0700
Well, let's do a test:
mysql CREATE TABLE testcount (product INT NOT NULL
As far as I know, they should be the same. The only case in which I can see
count(colname) would be slower is if it doesnt count rows that contain null
values for that column (this is only a guess, I dont know if count does that
or not).
ryan
Would count(*) show the same performance as count(1
_wape.jpg','','','',2,1,'A');
I got the follow error
ERROR 1136: Column count doesn't match value count at row 1
WHY IS THAT?
Amy
Because your column count doesn't match value count.
You have 18 columns, and 16 values.
--
Gerald L. Clark
[EMAIL PROTECTED
','','','',2,1,'A');
I got the follow error
ERROR 1136: Column count doesn't match value count at row 1
WHY IS THAT?
Amy
count(*) from mybuffer where bourse='NAS';
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.01 sec)
!!??
If I remove the count() :
mysql select * from mybuffer where bourse='NAS
Alex,
can you print the EXPLAIN SELECT ... for the count(*) and the other query?
Regards,
Heikki
At 12:03 PM 5/9/01 +0200, you wrote:
Bonjour,
Mysql-3.23.37 with latest patch (patch of the patch from Heikki :)
) under Solaris 2.8 (SPARC 64bit).
I have a table :
CREATE TABLE
Heikki,
HT can you print the EXPLAIN SELECT ... for the count(*) and the other query?
mysql explain select count(*) from mybuffer where bourse='NAS';
+--+--+---+---+-+---+--+-+
| table| type | possible_keys | key
= clust_rec;
+ }
}
/* We found a qualifying row */
.
At 02:40 PM 5/9/01 +0200, you wrote:
Heikki,
HT can you print the EXPLAIN SELECT ... for the count(*) and the other query?
mysql explain select
I have a table which contains a date column and an order_number column. I
need to formulate a query syntax to return the total number of orders for
each day in a given month (if any). Can someone help me with the syntax
please as I've been stumbling around with it all day without success.
Many
(date) as d,
SUM(order_number)
from
table1
group by d
if you want to count the rows per day (which i think is what you're after),
use:
select
DAYOFMONTH(date) as d,
count(order_number)
from
table1
group by d
you probably want to add in a where clause
around with it all day without success.
Many thanks, Graham
select data_column,count(*) as number_of_orders
from my_table
where MONTH(date_column)=#
group by TO_DAYS(date_column);
and you replace the # with the month number you are looking for
regards,
thalis
an error which is column count doesn't match value count at
row 1
I suspect this has something to do with an auto-increment ID field and/or a
primary key on ID.
Does anyone know how I can overcome this?
I have also used replace into table_2 select * from table_1 with the same
problems. what I am
Hi Suzanne,
When you say:
insert into table_2 select * from table_1
alls fine. All the records and fields will get
copied.
But when you say:
insert into table_2 select fieldname_1 from table_1
you are just selecting fieldname_1 from table_1
while
table_2 consists of fieldname_2
OK, I have a perl script using mysql that pulls up a bunch of numbers
for a table. Problem is it's taking a long time because of the number of
select calls I'm making.
Basically, I have a sequence of calls of the form:
SELECT COUNT(*) FROM table WHERE conditions
(execute, get value of count
I think this is an interesting question. Note the following:
count(col1) will tell you the number of non-null occurrences of col1
col1=value will return 1 is col1=value, 0 if col1value
1/0 will return null
thus,
count(1/(col1=value)) will tell you the number of occurences of value in
col1
I have a table, which keeps track of member information (including which
member referred the member). To get a count of the # of referrals for member
25, my query is:
SELECT COUNT(*) FROM members WHERE ref1 = 25;
To get a list of the top referers and the # of referrals they have, my query
why are you using distinct(ref1)?
also count(*) is ambiguous in this case. should be count(a.*).
you have a ref1 column in both tables, but you don't join them on it. that's
kind of confusing but won't cause the problem. member_id is the primary key
on the members table, right? Otherwise you
At 2:31 PM -0800 3/22/01, Daren Cotter wrote:
I have a table, which keeps track of member information (including which
member referred the member). To get a count of the # of referrals for member
25, my query is:
SELECT COUNT(*) FROM members WHERE ref1 = 25;
To get a list of the top referers
Actually, I did get it figured out...it was a small mistake
SELECT DISTINCT(a.ref1) AS member_id, count(*) AS count, b.password,
concat(UCASE(SUBSTRING(b.first_name,1,1))
, LCASE(SUBSTRING(b.first_name,2,LENGTH(b.first_name AS name, b.email,
b.html_mail, b.ref1, DATE_FORMAT(b.signup_
date
yet another argument for sub-selects :)
Paul DuBois wrote:
At 2:31 PM -0800 3/22/01, Daren Cotter wrote:
I have a table, which keeps track of member information (including which
member referred the member). To get a count of the # of referrals for member
25, my query is:
SELECT COUNT
Hi Folks,
i have a question regarding the handling of the count()
option in a sql-statement.
i have the following statement:
select distinct(ip), count(*) from log group by ip
This gives me a table with 2 columns and i want to
it sorted by the count(*) column. How would i do that??
...order
Hi,
On Fri, Apr 20, 2001 at 03:01:25PM +0200, [EMAIL PROTECTED] wrote:
i have a question regarding the handling of the count()
option in a sql-statement.
i have the following statement:
select distinct(ip), count(*) from log group by ip
This gives me a table with 2 columns and i want
[EMAIL PROTECTED] wrote:
i have a question regarding the handling of the count()
option in a sql-statement.
i have the following statement:
select distinct(ip), count(*) from log group by ip
This gives me a table with 2 columns and i want to
it sorted by the count(*) column. How would i do
I can make this query: select a from table.
I can count it: select count(a) from table.
I can select it: select distinct a from table.
But how can i count it?
select count(distinct a) from table doesn't works
Z
-
Before
On Fri, Apr 13, 2001 at 12:37:40PM +0200, Z_da_eXTaZie wrote:
I can make this query: select a from table.
I can count it: select count(a) from table.
I can select it: select distinct a from table.
But how can i count it?
select count(distinct a) from table doesn't works
It works for me
hi,
I need to make a select like above in the subject count distinct (I need the
count of the different values of a coloumn)
Hope you guys won't let me die silly!
:-)
greetings from germany
Micha
--
A train station is a station where a train stops
But what the hell is a workstation
It should work fine as printed...but you'll need mysql 3.23.xx
On Fri, 6
Apr 2001, Temeschinko, Michael wrote:
hi,
I need to make a select like above in the subject count distinct (I need the
count of the different values of a coloumn)
Hope you guys won't let me die silly
news (also
none), I use left join. Here is my query:
SELECT news.NewsID, news.headline, news.story, news.stmp,
count(comment.NewsID) AS nrcomments, pics.align FROM uudised
left JOIN comment ON comment.newsid=news.newsid
left join picsdb.pics on news.stmp=pics.stmp
GROUP BY news.newsid ORDER
,orders.ship_name,orders.order_date,
orders.order_total,orders.order_complete,orders.bad,
COUNT(DISTINCT planordermain.id) AS plans,COUNT(DISTINCT
accentorderdetail.id) AS accents
FROM orders,accentorderdetail,planordermain
WHERE ((orders.id=accentorderdetail.id) AND
(orders.id=planordermain.order_id))
GROUP
Alex,
yes, SHOW TABLE STATUS returns only approximate statistics about the
row count for Innobase tables. These statistics are used in the SQL
optimization.
From section 8.7 in the MySQL manual:
...snip
SHOW TABLE STATUS FROM TEST LIKE 'CUSTOMER'
if you have created
,orders.ship_name,orders.order_date,
orders.order_total,orders.order_complete,orders.bad,
COUNT(DISTINCT planordermain.id) AS plans,COUNT(DISTINCT accentorderdetail.id) AS
accents
FROM orders,accentorderdetail,planordermain
WHERE ((orders.id=accentorderdetail.id) AND (orders.id=planordermain.order_id))
GROUP
7 4 8 2
5 9 5 9 1
I would like to make a single query wich will return me how many (count?)
1,2,3,4,5,6,7,8,9 from all thre variables (q1a, q1b and q1c) are in Gender 1 or 2.
I'd like to have table with count to look something like this:
q1a OR q1b OR q1c=1 AND Gender
Hi there,
Doesn't seem to be a FAQ so here goes with a question
My MySQL manual has the following to say about how to count the number
of distinct values in a particular column
COUNT(DISTINCT expr,[expr...])
Returns a count of the number of different values.
mysql select COUNT(DISTINCT results
I see in your 'Explain's, the 2 queries use different indexes, the fast
one uses soc_date_idx, and the slower one uses q_idx. The trick,
perhaps is to force
soc_date_idx to be used in the 2nd case. (Adding ORDER BY soc_date might
do it, 'soc_date=X and (queue_id=Y and server_id=Z) ) might do
some more information that may prove useful...
If I do a select count(*) with the date set to 2001-02-10 then the soc_date
index is used and the query runs really quickly
mysql explain select count(*) from log where queue_id = 5 and soc_date =
'2001-02-10
in WHERE clauses.
Patrick
-Message d'origine-
De : Robin Keech [mailto:[EMAIL PROTECTED]]
Envoy : mardi 20 fvrier 2001 16:15
: '[EMAIL PROTECTED]'
Objet : RE: Slow Select count(*) - Second Post
Thanks for your response,
What benefit would that give me? Would a combined index be faster? I
the q_idx and see what that gives me.
Is there any way to re-write SQL to force the optimiser to use a certain
index?
Thanks again
Robin
-Original Message-
From: Jarmo Paavilainen [mailto:[EMAIL PROTECTED]]
Sent: 20 February 2001 15:50
To: Robin Keech
Subject: SV: Slow Select count
Thanks for all your help.
This has got the query time down to 5 seconds from 2 minutes!!
Much appreciated everyone.
Robin
-Original Message-
From: Tibor Simko [mailto:[EMAIL PROTECTED]]
Sent: 20 February 2001 16:46
To: Robin Keech
Cc: '[EMAIL PROTECTED]'
Subject: Re: Slow Select count
Hi,
Can anyone please explain the following?
I do two select count(*)'s from a big table, (21,000,000 + rows 6.5 Gb).
All columns in the where are indexed. The first select is very quick, the
second very slow.
The ONLY difference is the value of one column variable (queue_id), which
You can use:
SELECT Column_Name, count(Column_Name) FROM Table_Name GROUP BY
Column_Name
-Original Message-
From: Franz, Fa. PostDirekt MA [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 16, 2001 3:02 AM
To: [EMAIL PROTECTED]
Subject: COUNT(DISTINCT Column_Name)
Hi Everybody
| 2 | 1 |
| 2001-02-04 | 2 | 2 |
| 2001-02-06 | 1 | 2 |
++-+---+
5 rows in set (0.04 sec)
mysql SELECT COUNT(date) FROM Table2;
+-+
| COUNT(date) |
+-+
| 5 |
+-+
1 row
is as following
maker pc.id
a1
a2
a3
b4
b5
I need to retrieve the maker which include at least 2 pc.id.
I try to write in this form, but it is error!
select maker from table1
where count(pc.id) 2
group by maker.
thank you
Danyu
Hi--
I'm running an art archive, and for the artist listing pages I have to
come up with a query something like the following:
select name,dirname,sortname,pictures.artistid,count(pictures.artistid)
as imgnum,avg(rating) as rateavg,max(uploaded) as lastmod
from artists,pictures
,sortname,pictures.artistid,count(pictures.artistid)
as imgnum,avg(rating) as rateavg,max(uploaded) as lastmod
from artists,pictures,ratings
where artists.id=pictures.artistid and artists.id=ratings.artistid
group by sortname
order by imgnum desc
There are three tables involved: "ar
SELECT
YEAR(batches.date) AS date_year,
MONTH(batches.date) AS date_month,
COUNT(batches.batch_nr) AS batch_count,
SUM(batches.size) AS size_sum,
COUNT(testresults.id) AS testresults_count
FROM
batches
LEFT OUTER JOIN testresults ON
(testresults.batch_nr=batches.batch_nr
SELECT
YEAR(batches.date) AS date_year,
MONTH(batches.date) AS date_month,
COUNT(batches.batch_nr) AS batch_count,
SUM(batches.size) AS size_sum,
COUNT(testresults.id) AS testresults_count
FROM
batches
LEFT OUTER JOIN testresults ON
(testresults.batch_nr=batch
dear all:
I need some help from you.
The dataset is as following
maker pc.id
a1
a2
a3
b4
b5
I need to retrieve the maker which include at least 2 pc.id.
I try to write in this form, but it is error!
select maker from table1
where count(pc.id) 2
group by maker.
thank
Hello,
I want to know the number of rows in a JDBC-ResulSet just by calling a
function like
it's done in PHP with
mysql_num_rows($result)
There seems to be no such function in the ResultSet interface as
defined in Java 2.
cheers
Oliver Doepner
901 - 952 of 952 matches
Mail list logo
