Jerry Swanson wrote:
| Field| Type
| Null | Key | Default | Extra |
+--+--+--+-+-++
| id | in
Jerry,
You could also have 3 rows returned by running the following:
select count(*)
from account
where status in ('received','send','canceled')
group by status
;
This will return 3 rows, similar to the following
+-+---+
| status | count(*) |
+-
> I have table account (see below). I need to get count of received,
> count of send and cound of cancelled records. I know that I can do
> this in 3 queries.
> #1. select count(*) from account where status='received';
> #2. select count(*) from account where status='send';
> #3. select count(*) fr
Jerry Swanson wrote:
[...]
I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account where s
[snip]
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from a
This is what I was looking for. Why the query is call cross-tab?
TH
On Thu, 13 Jan 2005 02:09:45 +, Ian Sales <[EMAIL PROTECTED]> wrote:
> Jerry Swanson wrote:
>
> >| Field| Type
> > | Null | Key | Default | Extra |
> >+--+-
What query suppose to be faster? Jan or Dave?
Jan Solution:
SELECT
date,
SUM(IF(CONCAT(status,id) LIKE 'received%',1,0)) as received,
SUM(IF(CONCAT(status,id) LIKE 'send%',1,0)) as send,
SUM(IF(CONCAT(status,id) LIKE 'cancelled%',1,0)) as cancelled
FROM
account
GROUP BY date;
On Wed, 12 Jan 2005
[snip]
This is what I was looking for. Why the query is call cross-tab?
[/snip]
It is called a crosstab because of the way the reults resemble a results
table. Here is an article that shows the crosstab in larger use
http://www.evolt.org/article/Using_MySQL_and_PHP_to_Present_Excel_Spread
sh
[snip]
What query suppose to be faster? Jan or Dave?
[/snip]
Queries using equalities are generally faster than LIKE. YMMV.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I'm a MySQL newbie, though I've worked with SQL Server and other RDBMS for a
long time. That said, I'm *guessing* that the stored proc solution would be
the fastest, since its execution plan is precomputed, but a quick look seems
to say that stored procs are only available in v5, still in alpha.
O
I would think that the Jan solution should execute in a single pass
through the table. The Dave (subquery) version will probably need to make
3 passes through the table get the same results.
As was mentioned in another response, the inequality comparisons
(=,<,>,etc.) are much faster than a LI
What is the difference between '='' and '<>'?
TH
On Thu, 13 Jan 2005 09:13:07 -0500, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> I would think that the Jan solution should execute in a single pass through
> the table. The Dave (subquery) version will probably need to make 3 passes
> through
[snip]
What is the difference between '='' and '<>'?
[/snip]
= EQUAL TO
<> NOT EQUAL TO
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
I would think that the Jan solution should execute in a single pass
through the table. The Dave (subquery) version will probably need to make 3 passes through the table get the same results.
- er, it's Ian, not Jan :-)
As was mentioned in another response, the inequalit
Jay Blanchard wrote:
[snip]
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. s
[snip]
What is the purpose of DISTINCT here?
[/snip]
It's a hold over from other RDBMSs that I have worked with in the past.
The syntax is still valid and I pretty much automatically type it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http:/
Ian Sales wrote:
- I always concatenate the wanted criteria (status, in this case) with a
unique ID field in order to be absolutely certain I'm counting every
instance of each value of status. I've not investigated to see whether I
*really* need to do this, but it feels safer...
- ian
Then that
> I have table account (see below). I need to get count of received,
> count of send and cound of cancelled records. I know that I can do
> this in 3 queries.
> #1. select count(*) from account where status='received';
> #2. select count(*) from account where status='send';
> #3. select count(*) fr
OK, just for yucks, I tried two versions of this in the SQL Server Query
Analyzer. The group by version had 1/3 the cost of the subquery version. The
last_name field had a clustered index.
I then did the same thing in MySQL, and lacking any other cost measurement,
looked at execution time. Before
"Eko Budiharto" <[EMAIL PROTECTED]> wrote:
> I am trying to find how to use command "select count(*)". I tried it in
> mysql query console, it shows the number of my rows in my table. But
> when I tried it in perl with $sth->do("select count(*) from mytable")
> and run the perl script from DOS con
On Fri, 27 Sep 2002 08:16:49 -0400
"Michael J. Fuhrman" <[EMAIL PROTECTED]> wrote:
> Hello All,
>
> I seem to be running into a very strange problem.
>
> In the mySQL command line interface I issue SELECT COUNT (*) FROM
> test; and get --> ERROR 1064: You have an error in your SQL syntax
> near
Try without the blank
count(*) instead of count (*)
Easy mistake to make i guess, but remember that count() is a function and
should have brackets.
Regards
Torbjørn
On Fri, 27 Sep 2002, Michael J. Fuhrman wrote:
> Hello All,
>
> I seem to be running into a very strange problem.
>
> In the myS
Michael J. Fuhrman wrote:
> Hello All,
>
> I seem to be running into a very strange problem.
>
> In the mySQL command line interface I issue SELECT COUNT (*) FROM
> test; and get --> ERROR 1064: You have an error in your SQL syntax
> near '(*) from test' at line 1.
>
> Below is the test table:
On Fri, 27 Sep 2002, Michael J. Fuhrman wrote:
> Hello All,
>
> I seem to be running into a very strange problem.
>
> In the mySQL command line interface I issue SELECT COUNT (*) FROM
The correct syntax is SELCT COUNT(*) ie, there's no space between SELECT
and the (*).
Andy
--
Michael,
Friday, September 27, 2002, 3:16:49 PM, you wrote:
MJF> I seem to be running into a very strange problem.
MJF> In the mySQL command line interface I issue SELECT COUNT (*) FROM
MJF> test; and get --> ERROR 1064: You have an error in your SQL syntax
MJF> near '(*) from test' at line 1.
there is an error in your sql statement :
your origin sql is:SELECT COUNT (*) FROM test;
you should delete the space between 'count' and '(*)'.
the correct sql is :SELECT COUNT(*) FROM test;
=== £º===
>Hello All,
>
>I seem to be running into a very strange problem.
>
>In the
>Hi,
>I have these 3 tables which hold order information
>for a web site.
>
>One table holds the shipping info/credit cart stuff,
>another one holds one type of item ('home accents'),
>and the other one holds house plans.
>
>When someone orders these items, due to the options on
>house plans, I mu
[snip]
Apparently, "SELECT COUNT (*) FROM " is not valid syntax according
to mysql ? where as "SELECT COUNT(*) FROM " is ?
[/snip]
The syntax is the same for all function contained in parentheses in MySQL,
and as far as I can recall, in database systems other than MySQL (Oracle and
M$SQL
On Thursday, 27. June 2002 13:36, Per Jessen wrote:
> Hi,
>
> I am in the process of running ecperf using mysql, and ran into a
> small problem regarding SELECT COUNT - usd in some of the ecperf
> source.
> Apparently, "SELECT COUNT (*) FROM " is not valid syntax according
> to mysql ? where
> -Original Message-
> From: Jay Blanchard [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, June 27, 2002 2:11 PM
>
> [snip]
> Apparently, "SELECT COUNT (*) FROM " is not valid syntax according
> to mysql ? where as "SELECT COUNT(*) FROM " is ?
> [/snip]
>
> call and the opening par
[EMAIL PROTECTED] wrote:
I have a curious situation I was hoping someone could shed some light on.
mysql> select count(*) table;
+---+
| table |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
mysql> select count(*) from table;
+--+
| count(*) |
+--+
|25965 |
+
select count(*) table; gives me a syntax error in 4.0 and 4.1 so I'm not
sure how that worked. Something similar would be select count(*) t;
which uses the shortcut alias syntax. It's the same as doing select
count(*) as t;
Simon Garner wrote:
[EMAIL PROTECTED] wrote:
I have a curious situation
just give a it try select count(columnname) from table;
Eric Bergen <[EMAIL PROTECTED]> wrote:select count(*) table; gives me a syntax
error in 4.0 and 4.1 so I'm not
sure how that worked. Something similar would be select count(*) t;
which uses the shortcut alias syntax. It's the same as doing
The following may assist: (three-fifths down)
http://www.mysql.com/doc/G/r/Group_by_functions.html
Note that if you are using MySQL Version 3.22 (or earlier) or if you are
trying to follow ANSI SQL, you can't use expressions in GROUP BY or ORDER BY
clauses. You can work around this limitation by
try in simple programming skill at
1) if your script language uses the pattern
while (resultSet.next())
{
out.println(resultSet.getFirstColumn();
...
...
...
counter++;
}
out.println("Total number of records"+counter);
2) if you do not want that, you can also try this
sql
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!
> :-)
Hi I think you would do this :
SELECT
sf_conferences.id,
sf_conferences.name,
count(*) AS `count`
FROM
((
LEFT JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk)
LEFT sf_threads ON sf_forums.id = sf_threads.forumidfk)
LEFT JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk
GROU
Thank You Richard! Merci beaucoup.
wConti
Richard-175 wrote:
>
> Hi I think you would do this :
>
> SELECT
> sf_conferences.id,
> sf_conferences.name,
> count(*) AS `count`
> FROM
> ((
> LEFT JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk)
> LEFT sf_threads ON sf_forums.id = sf_t
* compuserve aka Bill Stennett
> I have the following situation:
>
> the DB has three tables 'users', 'links' and 'searches'. Each table has a
> common key named 'userid'
This does not match the table/column names you describe below... are you
trying to confuse us? ;)
> What I want to do is, for
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 21 January 2004 16:51
To: [EMAIL PROTECTED]
Cc: compuserve
Subject: Re: select count from three tables
* compuserve aka Bill Stennett
> I have the following situation:
>
> the DB has three tables 'users
* Bill - compuserve
> Thanks for the suggestion but unfortunately this seems to produce the
> product of the two tables `lghyperlink` and `lgsearch`.
Yes, sorry about that.
[...]
> Any other suggestions gratefully received - maybe this has to be
> done in two queries and the data manipulated in t
thanks for taking the time to look
best
bill
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 21 January 2004 20:19
To: [EMAIL PROTECTED]
Cc: Bill - compuserve
Subject: Re: select count from three tables
* Bill - compuserve
> Thanks for the suggestion
- Dathan Vance Pattishall
- Sr. Programmer and mySQL DBA for FriendFinder Inc.
- http://friendfinder.com/go/p40688
-->-Original Message-
-->From: Hsiu-Hui Tseng [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, November 06, 2003 3:41 PM
-->To: [EMAIL PROTECTED]
-->Subject: select count(*
At 15:41 -0800 11/6/03, Hsiu-Hui Tseng wrote:
Hi,
select count(*) is very slow in innodb (because it is a table scan). Is
there any difference if I change it to select count(column).
I did some test and some times select count(*) is really slow and some time
select count(column) is slow. Could any
You are not doing "select count(*)"
You are doing "select count ( *)"
Get rid of the spaces before the "("
Fatt Shin wrote:
Hi,
I'm running MySQL 4.0.13, connecting from PowerBuilder 9 using ODCB
Connector 3.51.
I'm facing a problem where whenever I issue a SELECT COUNT(*) statement
from PowerBui
: Friday, August 08, 2003 12:17 AM
To: Fatt Shin
Cc: [EMAIL PROTECTED]
Subject: Re: select count(*) Problem in PowerBuilder Thru MySQL ODBC
3.51
You are not doing "select count(*)"
You are doing "select count ( *)"
Get rid of the spaces before the "("
Fatt Shin w
Fatt Shin wrote:
> Yes, found the problem, you are right. Thanks.
> But this is something caused by powerbuilder, in my code I never put
> any space between count(*), but when it goes to odbc, an space is
> added. Any idea how to solve this?
Wouldn't sum(1) return the same count -theoratically fast
47 matches
Mail list logo
