COUNT(DISTINCT R1,R2) within an IF statement?
Hi, I have a query which looks like this... SELECT BLEAH, COUNT(DISTINCT R1,R2) FROM T1 WHERE FK = 1 GROUP BY BLEAH ; Lets say that over 10 rows where FK = 1 it counts 5 distinct R1-R2 pairs in a single 'BLEAH' group BLEAH = 'Y'. Now I want to search the table for all FK's with the same number of R1-R2 pairs in the BLEAH group, and I do it like this... SELECT FK, COUNT(DISTINCT IF(BLEAH='Y',CONCAT(R1,-,R2),NULL)) AS BLING GROUP BY FK HAVING BLING = 5; The problem is that CONCAT. I don't like the look of it. It makes me think that the optimizer dosn't stand a chance, which I am not sure if it does anyway (even though I have a index x (R1,R2). I would like to be able to say something like... ... IF(BLEAH='Y',R1,R2,NULL) ... But of course that messes up the IF syntax. Or I would like to say... ... IF(BLEAH='Y',ROW(R1,R2),NULL) ... But DISTINCT balls ERROR 1241 (21000): Operand should contain 1 column(s), which seems a bit strange, as in the first query we are passing it two columns. Same error occurs with this syntax... ... IF(BLEAH='Y',(R1,R2),NULL) ... Am I stuck doing my CONCAT? I like to keep the syntax general, as for a particular PK, BLEAH could have several values, in which case I stack up my thingies... SELECT FK, COUNT(DISTINCT IF(BLEAH='Y',CONCAT(R1,-,R2),NULL)) AS BLING, COUNT(DISTINCT IF(BLEAH='N',CONCAT(R1,-,R2),NULL)) AS BLANG, GROUP BY FK HAVING BLING = 5 AND BLANG = 5; For example. Would the COUNT(DISTINCT above get optimized if I was only looking at the values in one (indexed) column (R1 for example)? Cheers, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query: count(distinct field1 max(fieldn)) where?
I need some help with a complex query of mine. The query in question looks like this: SELECT DISTINCT film_film.filmid, titel, pdf, termin, aar, film_serier.serieid, serienamn, screener, har_affisch, har_bilder, max( datum ) FROM ( ( ( `film_film` LEFT JOIN film_rel_regissoerer ON film_film.filmid = film_rel_regissoerer.filmid_relid ) LEFT JOIN film_regissoerer ON film_rel_regissoerer.regissoerid_relid = film_regissoerer.regissoerid ) LEFT JOIN film_visningar ON film_film.filmid = film_visningar.filmid ) LEFT JOIN film_serier ON film_visningar.serieid = film_serier.serieid GROUP BY titel Earlier, before I added the max(datum) to the query, i could get the number of rows by doing: --- SQL --- SELECT count( DISTINCT film_film.filmid, titel, IF ( aar IS NULL , '', aar ), IF ( termin IS NULL , '', termin ) ) AS antal FROM ( ( ( `film_film` LEFT JOIN film_rel_regissoerer ON film_film.filmid = film_rel_regissoerer.filmid_relid ) LEFT JOIN film_regissoerer ON film_rel_regissoerer.regissoerid_relid = film_regissoerer.regissoerid ) LEFT JOIN film_visningar ON film_film.filmid = film_visningar.filmid ) LEFT JOIN film_serier ON film_visningar.serieid = film_serier.serieidLIMIT 0 , 30 The if-clauses where added because count returned wrong number of rows when there where NULL-values in the result What I need to do is to count the number of rows in the first query. But I can't figure how. Adding a max(datum) to the count-list simply wont do it; reports sql-errors Extremely thankful for some feedback. Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
COUNT(DISTINCT ...)
HELP! I'm getting the following error: -- Error (SQL): SELECT date, subject, location, private, id, duration, dategroup_id, COUNT(DISTINCT subject) AS appointment_count, inituserid FROM mgw_calendar WHERE userid=1 AND SUBSTRING(date,1,8) = '20030312' GROUP BY date, subject, location, private, id, duration, dategroup_id, inituserid ORDER BY date Error (MSG): You have an error in your SQL syntax near 'DISTINCT subject) AS appointment_count, inituserid FROM mgw_calendar WHERE useri' at line 1 --- I'm using MySQL 3.22.32 does COUNT(DISTINCT ...) not work with this version? Thanks, Bob Sawyer --- Pixellated! Design:Develop:Deliver www.pixellated.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: COUNT(DISTINCT ...)
At 16:42 -0500 3/12/03, Bob Sawyer wrote: HELP! I'm getting the following error: -- Error (SQL): SELECT date, subject, location, private, id, duration, dategroup_id, COUNT(DISTINCT subject) AS appointment_count, inituserid FROM mgw_calendar WHERE userid=1 AND SUBSTRING(date,1,8) = '20030312' GROUP BY date, subject, location, private, id, duration, dategroup_id, inituserid ORDER BY date Error (MSG): You have an error in your SQL syntax near 'DISTINCT subject) AS appointment_count, inituserid FROM mgw_calendar WHERE useri' at line 1 --- I'm using MySQL 3.22.32 does COUNT(DISTINCT ...) not work with this version? No, not until 3.23.2. Thanks, Bob Sawyer --- Pixellated! Design:Develop:Deliver www.pixellated.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select count distinct
I'm stuck right now with using v. 3.22.32. Yes, I know I need to upgrade, but I can't for various reasons. Anyway, I need to do a select count(distinct column) from table query, but can't since this version doesn't support it. How can I do this? Thanks, Brian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select count distinct
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 using an alias for the expression: mysql SELECT id,FLOOR(value/100) AS val FROM tbl_name GROUP BY id,val ORDER BY val; C: -Original Message- From: Brian Warn [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 4:31 PM To: MySQL List Subject: select count distinct I'm stuck right now with using v. 3.22.32. Yes, I know I need to upgrade, but I can't for various reasons. Anyway, I need to do a select count(distinct column) from table query, but can't since this version doesn't support it. How can I do this? Thanks, Brian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
bug in count(distinct(putafieldnamehere)) ?
Hi, can it be that there is a bug in count(distinct(putafieldnamehere)) ? After restarting the server it runs, but after some time it doesnt return any result-rows. A count(putafieldnamehere) runs without problems in this situation. Thanx for any sugesstions to solve this problem ... mysql,database,query,bug - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: bug in count(distinct(putafieldnamehere)) ?
Ok, its not in count(distinct()). It always happends if temptables are used, but only if the server runs some minutes/hours/days :( -Ursprüngliche Nachricht- Von: Maverick [mailto:[EMAIL PROTECTED]] Gesendet: Donnerstag, 7. Februar 2002 16:48 An: [EMAIL PROTECTED] Betreff: bug in count(distinct(putafieldnamehere)) ? Hi, can it be that there is a bug in count(distinct(putafieldnamehere)) ? After restarting the server it runs, but after some time it doesnt return any result-rows. A count(putafieldnamehere) runs without problems in this situation. Thanx for any sugesstions to solve this problem ... mysql,database,query,bug - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
bug in count(distinct(putafieldnamehere)) ?
Hi, can it be that there is a bug in count(distinct(putafieldnamehere)) ? After restarting the server it runs, but after some time it doesnt return any result-rows. A count(putafieldnamehere) runs without problems in this situation. Thanx for any sugesstions to solve this problem ... mysql,database,query,bug - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: bug in count(distinct(putafieldnamehere)) ?
Ok, its not in count(distinct()). It always happends if temptables are used, but only if the server runs some minutes/hours/days :( -Ursprüngliche Nachricht- Von: Maverick [mailto:[EMAIL PROTECTED]] Gesendet: Donnerstag, 7. Februar 2002 16:48 An: [EMAIL PROTECTED] Betreff: bug in count(distinct(putafieldnamehere)) ? Hi, can it be that there is a bug in count(distinct(putafieldnamehere)) ? After restarting the server it runs, but after some time it doesnt return any result-rows. A count(putafieldnamehere) runs without problems in this situation. Thanx for any sugesstions to solve this problem ... mysql,database,query,bug - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count(distinct
Il Tue, 08 Jan 2002 13:23:09 -0500, Stephen Abshire andava dicendo... SELECT count(distinct ip) FROM pole_voti It does'nt work. Why ? PhpMyAdmin says: You have an error in your SQL syntax near 'distinct ip) FROM pole_voti' at line 1 I don't have MySQL on the computer I am on to test this but you might try rewriting it this way: select count(distinct(ip)) from pole_voit Already done. It does'nt work to. Ilic. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: count(distinct
F:\MySQL\binmysql -uroot -e select distinct count(*) from user \G mysql
*** 1. row ***
count(*): 4
Xi2 ('sE-'tü)
-Original Message-
From: Ilic [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 5:54 AM
To: [EMAIL PROTECTED]
Subject: Re: count(distinct
Il Tue, 08 Jan 2002 13:23:09 -0500, Stephen Abshire andava dicendo...
SELECT count(distinct ip) FROM pole_voti
It does'nt work. Why ?
PhpMyAdmin says: You have an error in your SQL syntax near
'distinct ip)
FROM pole_voti' at line 1
I don't have MySQL on the computer I am on to test this but you
might try rewriting it this way:
select count(distinct(ip)) from pole_voit
Already done. It does'nt work to.
Ilic.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: count(distinct
* Ilic SELECT count(distinct ip) FROM pole_voti It does'nt work. Why ? PhpMyAdmin says: You have an error in your SQL syntax near 'distinct ip) FROM pole_voti' at line 1 I don't have MySQL on the computer I am on to test this but you might try rewriting it this way: select count(distinct(ip)) from pole_voit Already done. It does'nt work to. COUNT(DISTINCT was introduced in version 3.23.2: URL: http://www.mysql.com/doc/N/e/News-3.23.2.html Maybe your server is a bit old...? -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count(distinct
I am inclined to think (actually guess) it may be the version of MySQL you are using. I am using MySQL 3.23.39 and I am able to successfully execute: select count(distinct field) or select count(distinct(field)) I tried to make a quick check of the docs to see if a certain version was required but I was unable to find anything. Original Message Follows From: Ilic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: count(distinct Date: Wed, 9 Jan 2002 14:54:12 +0100 Il Tue, 08 Jan 2002 13:23:09 -0500, Stephen Abshire andava dicendo... SELECT count(distinct ip) FROM pole_voti It does'nt work. Why ? PhpMyAdmin says: You have an error in your SQL syntax near 'distinct ip) FROM pole_voti' at line 1 I don't have MySQL on the computer I am on to test this but you might try rewriting it this way: select count(distinct(ip)) from pole_voit Already done. It does'nt work to. Ilic. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: count(distinct
Same here: F:\MySQL\binmysql -uroot -e select count(distinct Host) from user \G mysql *** 1. row *** count(distinct Host): 2 F:\MySQL\binmysql -v Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 to server version: 3.23.39-nt Xi2 -Original Message- From: Stephen Abshire [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 7:21 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: count(distinct I am inclined to think (actually guess) it may be the version of MySQL you are using. I am using MySQL 3.23.39 and I am able to successfully execute: select count(distinct field) or select count(distinct(field)) I tried to make a quick check of the docs to see if a certain version was required but I was unable to find anything. Original Message Follows From: Ilic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: count(distinct Date: Wed, 9 Jan 2002 14:54:12 +0100 Il Tue, 08 Jan 2002 13:23:09 -0500, Stephen Abshire andava dicendo... SELECT count(distinct ip) FROM pole_voti It does'nt work. Why ? PhpMyAdmin says: You have an error in your SQL syntax near 'distinct ip) FROM pole_voti' at line 1 I don't have MySQL on the computer I am on to test this but you might try rewriting it this way: select count(distinct(ip)) from pole_voit Already done. It does'nt work to. Ilic. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: count(distinct
COUNT(DISTINCT was introduced in 3.23.2: URL: http://www.mysql.com/doc/N/e/News-3.23.2.html -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
count(distinct
SELECT count(distinct ip) FROM pole_voti It does'nt work. Why ? PhpMyAdmin says: You have an error in your SQL syntax near 'distinct ip) FROM pole_voti' at line 1 Why ? Where is the error ? Ilic. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count(distinct
I don't have MySQL on the computer I am on to test this but you might try rewriting it this way: select count(distinct(ip)) from pole_voit Original Message Follows From: Ilic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: count(distinct Date: Tue, 8 Jan 2002 18:47:43 +0100 SELECT count(distinct ip) FROM pole_voti It does'nt work. Why ? PhpMyAdmin says: You have an error in your SQL syntax near 'distinct ip) FROM pole_voti' at line 1 Why ? Where is the error ? Ilic. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: count(distinct
The syntax is: SELECT DISTINCT http://www.mysql.com/doc/S/E/SELECT.html Xi2 -Original Message- From: Ilic [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 08, 2002 9:48 AM To: [EMAIL PROTECTED] Subject: count(distinct SELECT count(distinct ip) FROM pole_voti It does'nt work. Why ? PhpMyAdmin says: You have an error in your SQL syntax near 'distinct ip) FROM pole_voti' at line 1 Why ? Where is the error ? Ilic. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count(distinct
Ilic writes: SELECT count(distinct ip) FROM pole_voti It does'nt work. Why ? What's your MySQL version? Check the docs to see what version is required for count(distinct). I think it was added in 3.23.early, so it might be time to upgrade. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
any bugs with 3.23.36 regarding COUNT(DISTINCT ?
Hi, we're trying to recommend that clients upgrade to version 3.23.36, and wanted to ensure that it's stable. Particularly, have there been any bugs regarding any bugs regarding the use of COUNT(DISTINCT since then? In other words, if our software performs a COUNT(DISTINCT column) FROM SomeTable I just want to make sure we're recommending a good version. If stability/known-bugs can be found in a faq somewhere to answer this question, please let me know! thanks! Kevin Fries - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
COUNT(DISTINCT) -- is this correct behaviour?
Hi!
Thanks for the nice test case!
Next time you have as nice a test case, please send it to
[EMAIL PROTECTED] for fast treatment!
tommie == tommie [EMAIL PROTECTED] writes:
tommie count() gives 0 but count(distinct) gives 1. is this correct or
tommie a bug or is there something wrong with my SELECT?
mysql create table t1 (f1 int);
mysql insert into t1 values (1);
mysql create table t2 (f1 int,f2 int);
mysql select t1.f1,count(t2.f2) from t1
- left join t2 on t1.f1=t2.f1 group by t1.f1;
tommie +--+--+
tommie | f1 | count(t2.f2) |
tommie +--+--+
tommie |1 |0 |
tommie +--+--+
tommie 1 row in set (0.00 sec)
mysql select t1.f1,count(distinct t2.f2) from t1
- left join t2 on t1.f1=t2.f1 group by t1.f1;
tommie +--+---+
tommie | f1 | count(distinct t2.f2) |
tommie +--+---+
tommie |1 | 1 |
tommie +--+---+
tommie 1 row in set (0.01 sec)
Fix:
= sql/item_sum.cc 1.7 vs edited =
*** /tmp/item_sum.cc-1.7-18231 Sun Jan 28 21:35:50 2001
--- edited/sql/item_sum.cc Tue Jun 19 12:58:35 2001
***
*** 809,816
ListItem list;
/* Create a table with an unique key over all parameters */
for (uint i=0; i arg_count ; i++)
! if (list.push_back(args[i]))
! return 1;
count_field_types(tmp_table_param,list,0);
if (table)
{
--- 809,827
ListItem list;
/* Create a table with an unique key over all parameters */
for (uint i=0; i arg_count ; i++)
! {
! Item *item=args[i];
! if (list.push_back(item))
! return 1; // End of memory
! if (item-const_item())
! {
! (void) item-val_int();
! if (item-null_value)
! always_null=1;
! }
! }
! if (always_null)
! return 0;
count_field_types(tmp_table_param,list,0);
if (table)
{
***
*** 827,841
void Item_sum_count_distinct::reset()
{
! table-file-extra(HA_EXTRA_NO_CACHE);
! table-file-delete_all_rows();
! table-file-extra(HA_EXTRA_WRITE_CACHE);
! (void) add();
}
bool Item_sum_count_distinct::add()
{
int error;
copy_fields(tmp_table_param);
copy_funcs(tmp_table_param-funcs);
--- 838,857
void Item_sum_count_distinct::reset()
{
! if (table)
! {
! table-file-extra(HA_EXTRA_NO_CACHE);
! table-file-delete_all_rows();
! table-file-extra(HA_EXTRA_WRITE_CACHE);
! (void) add();
! }
}
bool Item_sum_count_distinct::add()
{
int error;
+ if (always_null)
+ return 0;
copy_fields(tmp_table_param);
copy_funcs(tmp_table_param-funcs);
= sql/item_sum.h 1.5 vs edited =
*** /tmp/item_sum.h-1.5-18231 Sat Jan 27 01:20:55 2001
--- edited/sql/item_sum.h Tue Jun 19 12:59:50 2001
***
*** 145,155
table_map used_table_cache;
bool fix_fields(THD *thd,TABLE_LIST *tables);
TMP_TABLE_PARAM *tmp_table_param;
public:
Item_sum_count_distinct(ListItem list)
:Item_sum_int(list),table(0),used_table_cache(~(table_map) 0),
! tmp_table_param(0)
{ quick_group=0; }
~Item_sum_count_distinct();
table_map used_tables() const { return used_table_cache; }
--- 145,156
table_map used_table_cache;
bool fix_fields(THD *thd,TABLE_LIST *tables);
TMP_TABLE_PARAM *tmp_table_param;
+ bool always_null;
public:
Item_sum_count_distinct(ListItem list)
:Item_sum_int(list),table(0),used_table_cache(~(table_map) 0),
! tmp_table_param(0),always_null(0)
{ quick_group=0; }
~Item_sum_count_distinct();
table_map used_tables() const { return used_table_cache; }
The above will be in the next MySQL release.
Regards,
Monty
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
COUNT(DISTINCT) -- is this correct behaviour?
count() gives 0 but count(distinct) gives 1. is this correct or a bug or is there something wrong with my SELECT? mysql create table t1 (f1 int); mysql insert into t1 values (1); mysql create table t2 (f1 int,f2 int); mysql select t1.f1,count(t2.f2) from t1 - left join t2 on t1.f1=t2.f1 group by t1.f1; +--+--+ | f1 | count(t2.f2) | +--+--+ |1 |0 | +--+--+ 1 row in set (0.00 sec) mysql select t1.f1,count(distinct t2.f2) from t1 - left join t2 on t1.f1=t2.f1 group by t1.f1; +--+---+ | f1 | count(distinct t2.f2) | +--+---+ |1 | 1 | +--+---+ 1 row in set (0.01 sec) regards, tommie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: COUNT(DISTINCT) -- is this correct behaviour?
[EMAIL PROTECTED] writes: count() gives 0 but count(distinct) gives 1. is this correct or a bug or is there something wrong with my SELECT? mysql create table t1 (f1 int); mysql insert into t1 values (1); mysql create table t2 (f1 int,f2 int); mysql select t1.f1,count(t2.f2) from t1 - left join t2 on t1.f1=t2.f1 group by t1.f1; +--+--+ | f1 | count(t2.f2) | +--+--+ |1 |0 | +--+--+ 1 row in set (0.00 sec) mysql select t1.f1,count(distinct t2.f2) from t1 - left join t2 on t1.f1=t2.f1 group by t1.f1; +--+---+ | f1 | count(distinct t2.f2) | +--+---+ |1 | 1 | +--+---+ 1 row in set (0.01 sec) regards, tommie Hi! Thanks for the bug report. We shall investigate it. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
count distinct
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 posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count distinct
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 on MySQL 3.23.36.. which version of MySQL are you running? G'luck, Peter -- I am jealous of the first word in this sentence. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select(count(distinct(status))
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? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select(count(distinct(status))
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! :-) greetings from germany Micha - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
COUNT(DISTINCT some_column)
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) from student; however on the MySQL versions I currently have access to (3.22.32 3.22.34) I get this: mysql select COUNT(DISTINCT SOME_COLUMN) from SOME_TABLE; ERROR 1064: You have an error in your SQL syntax near 'DISTINCT SOME_COLUMN) from SOME_TABLE' at line 1 whereas mysql select COUNT(SOME_COLUMN) from SOME_TABLE; works fine I am assuming that either this function is not available or that it is a bug that has been fixed in a later release (or is on a todo list somewhere). Can anybody fill me in? Thanks in advance CHEERS SAM - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: COUNT(DISTINCT Column_Name)
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 , I am using MySql 3.22.32 on LINUX. My problem is , that 'SELECT COUNT(DISTINCT Column_Name) FROM Table_Name doesn't work like described in the HTML-manual (7.4.13) . It even doesn't work at all. Is tthat a bug , am I stupid , or is there any workaround. Greetings Klaus Besuchen Sie uns auch im Internet: http://www.postdirekt.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem using COUNT DISTINCT together
Hi, Woh ! This is very strange. Any ideas ? mysql SELECT * FROM Table2; ++-+---+ | date | no_of_items | table1_id | ++-+---+ | 2001-02-04 | 3 | 1 | | 2001-02-05 | 2 | 1 | | 2001-02-06 | 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 in set (0.02 sec) mysql SELECT DISTINCT date FROM Table2; ++ | date | ++ | 2001-02-04 | | 2001-02-05 | | 2001-02-06 | ++ 3 rows in set (0.03 sec) mysql SELECT COUNT(DISTINCT date) FROM Table2; ERROR 1064: You have an error in your SQL syntax near 'DISTINCT date) FROM Table2' at line 1 mysql SELECT COUNT (DISTINCT date) FROM Table2; ERROR 1064: You have an error in your SQL syntax near '(DISTINCT date) FROM Table2' at line 1 mysql SELECT COUNT(DISTINCT 'date') FROM Table2; ERROR 1064: You have an error in your SQL syntax near 'DISTINCT 'date') FROM Table2' at line 1 FYI: ./mysql Ver 9.38 Distrib 3.22.32, for sun-solaris2.7 (sparc) Kind Regards, -- Frank __ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
