) FROM t1 t)
UNION
(SELECT t.id, t.name, SUM(val) FROM t2 t)
GROUP BY t.id, t.name;
That does not work in 5.0 either (at least in 5.0.96):
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax
or in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'GROUP BY t.AccountID,
I suspect your query has never been doing what you think at all, and you need to
select [fields] from (
select fields1 blahblah
UN
- Original Message -
> From: "Machiel Richards"
>
> ERROR 1064 (42000): You have an error in your SQL syntax; check the
> manual that corresponds to your MySQL server version for the right
> syntax to use near 'GROUP BY t.AccountID,
I suspect your query
shorter but equivalent query, you have:
(SELECT t.id, t.name, SUM(val) FROM t1 t)
UNION
(SELECT t.id, t.name, SUM(val) FROM t2 t)
GROUP BY t.id, t.name;
That does not work in 5.0 either (at least in 5.0.96):
ERROR 1064 (42000): You have an error in your SQL syntax; check the
=a.ID
LEFT JOIN client2 c ON t.ClientID=c.ID
LEFT JOIN tocname tc ON t.TOC=tc.toc
LEFT JOIN network n ON t.NetworkID=n.ID
WHERE t.toc=1
AND SUBSTRING(t.Day,1,7) >= '2013-08'
AND SUBSTRING(t.Day,1,7) <= '2013-11')
GROUP BY t.AccountID,
On Wed, Sep 19, 2012 at 6:56 PM, Rick James wrote:
>> my counts are 3 times too much.
> Without studying the code, I would guess that there is a JOIN between he data
> that needs COUNTing and the GROUP BY for the COUNT. That is, it collects
> more 'joined' rows before
2012/09/19 14:36 -0400, Larry Martell
MIN(ABS(Avg(bottom) - bottom))
Is not valid. It gives:
ERROR (HY000): Invalid use of group function
Yes, I had my doubts of that, for all that I suggested it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/m
> my counts are 3 times too much.
Without studying the code, I would guess that there is a JOIN between he data
that needs COUNTing and the GROUP BY for the COUNT. That is, it collects more
'joined' rows before counting. Fixing it will probably make the query even
messier.
&g
ottom) as Ranges
FROM data_cst
WHERE TRUE
AND data_cst.target_name_id IN (775, 776, 777, 778, 779, 780,
45, 44, 116, 117, 118, 119, 120, 121)
AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND
'2010-0
OK, I think I have this working. The last join was this:
JOIN (select id, target_name_id, ep,date_time from data_cst WHERE
target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117,
118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04
00:00:00' AND '2010-03-04 23:
-
>> bottom)) AS vb, Max(date_time) AS xt
>> FROM data_cst
>> WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116,
>> 117, 118, 119, 120, 121)
>> AND DATE(data_cst.date_time) = '2010-03-04'
>> GROUP BY target_name_id,
d IN (775, 776, 777, 778, 779, 780, 45, 44, 116,
> 117, 118, 119, 120, 121)
> AND DATE(data_cst.date_time) = '2010-03-04'
> GROUP BY target_name_id, ep, wafer_id
> HAVING count(*) < 999) AS st
> JOIN data_cst ON st.target_name_id = data_cst.target_name_id AND (nb =
d IN (775, 776, 777, 778, 779, 780, 45, 44, 116,
> 117, 118, 119, 120, 121)
> AND DATE(data_cst.date_time) = '2010-03-04'
> GROUP BY target_name_id, ep, wafer_id
> HAVING count(*) < 999) AS st
> JOIN data_cst ON st.target_name_id = data_cst.target_name_id AND (n
-04 00:00:00' AND '2010-03-04
> 23:59:59'
> AND data_target.id = data_cst.target_name_id
> GROUP BY rollup.Target, rollup.EP
> ORDER BY ABS(data_cst.bottom - rollup.`Avg Bottom`)
> LIMIT 1 -- <--
rollup is the name of the temp
b, vb, xt, bottom
FROM (SELECT Min(bottom) AS nb, Max(bottom) AS xb, MIN(ABS(Avg(bottom) -
bottom)) AS vb, Max(date_time) AS xt
FROM data_cst
WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117,
118, 119, 120, 121)
AND DATE(data_cst.date_time) = '20
AND data_target.id = data_cst.target_name_id
GROUP BY rollup.Target, rollup.EP
ORDER BY ABS(data_cst.bottom - rollup.`Avg Bottom`)
LIMIT 1 -- <--
> -Original Message-
> From: Larry Martell [mailto:larry.mart...@gmail.com]
> Sent: Tuesday, September 18, 2012 2:57 P
ta_target
WHERE data_target.name = rollup.Target
AND data_cst.ep = rollup.EP
AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59'
AND data_target.id = data_cst.target_name_id
GROUP BY rollup.Target, rollup.EP
ORDER BY ABS(data_cst.bottom - rollup.`Avg Bot
com
> Subject: Re: getting certain rows from a group by
>
> On Tue, Sep 18, 2012 at 2:05 PM, Rick James
> wrote:
> > For single query...
> >
> > Plan A:
> > Repeat the SELECT as once for each (MIN, AVG, etc) as a subquery in
> >WHERE bottom =
ail.com]
>> Sent: Tuesday, September 18, 2012 4:57 AM
>> To: peter.braw...@earthlink.net
>> Cc: mysql@lists.mysql.com
>> Subject: Re: getting certain rows from a group by
>>
>> On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley
>> wrote:
>> > On
MAX(date_time) as "LastRun",
>>> COUNT(*) as numonep,
>>> COUNT(DISTINCT target_name_id, ep, lot_id,
>>> data_file_id)-1 as reruns,
>>> COUNT(DISTINCT(lot_id)) as Lots,
>>> 3*STD(bottom)
Cc: mysql@lists.mysql.com
> Subject: Re: getting certain rows from a group by
>
> On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley
> wrote:
> > On 2012-09-18 5:53 AM, Larry Martell wrote:
> >>
> >> I have this query:
> >>
> >> SELECT data_t
maxbottom,
>> MIN(bottom) as minbottom,
>> MAX(date_time) as "LastRun",
>> COUNT(*) as numonep,
>> COUNT(DISTINCT target_name_id, ep, lot_id,
>> data_file_id)-1 as reruns,
>> COUN
OM data_cst
WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44,
116, 117, 118, 119, 120, 121)
AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND
'2010-03-04 23:59:59'
GROUP BY target_name_id, ep, wafer_id
HAVING count(*) < 999)
COUNT(DISTINCT target_name_id, ep, lot_id,
data_file_id)-1 as reruns,
COUNT(DISTINCT(lot_id)) as Lots,
3*STD(bottom) as Wafer3Sigma,
MAX(bottom) - MIN(bottom) as Ranges
FROM data_cst
WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44,
Thanks, it seems to be working now. I just discovered "WITH ROLLUP". It made me
very
happy on this project...
On 2/8/12 2:54 AM, Arthur Fuller wrote:
I'm not sure your method isn't working, but try changing changing the "to date" part to
"< '2012-02-08' " and see what you get.
HTH,
Arthur
I'm not sure your method isn't working, but try changing changing the "to
date" part to "< '2012-02-08' " and see what you get.
HTH,
Arthur
I am having a problem with select results that I don't understand. It seems to
be tied up with a GROUP BY statement. Forgive the complexity of the SQL, I
inherited some problematic data structuring.
If I use this statement:
SELECT lu_rcode_bucket.bucket AS 'BUCKET',
I am trying to optimize a query that uses a group by on a varchar(255)
column. The column has a large enough cardinality that a 10 character
partial index uniquely covers over 99% of all values. I was hoping
that this partial index would be able to help with the group by
(though obviously not as
You need to group by event_text, not obj_text:
select source_id ,event_text,count(*) from event_loc
group by source_id,event_text;
> Date: Tue, 8 Feb 2011 16:31:39 +0530
> From: adarsh.sha...@orkash.com
> To: mysql@lists.mysql.com
> Subject: Group By Problem
>
> Dear
3
performs 1
13456 Minister 1
I tried the below query :
*select source_id ,event_text,count(*) from event_loc group by
source_id,obj_text ;*
But displays only unique record_id rows but I want as
11:18:58 | 2011-01-16 11:13:04 |
+--+-+-+
8 rows in set (0.09 sec)
The problem is that album_id 81 is occuring two times in the list. So I
thought I should add a "group by" in the query:
mysql> select album_id, updated_at, created_at
gt; mysql> select album_id, updated_at, created_at from album_stats group by
>>> album_id order by updated_at desc limit 8;
>>
>> I believe that your problem is that the group by happens before the
>> order by. Since you're grouping, the updated_at column is not
>&
On Monday 17 January 2011 09:53, Steve Meyers wrote:
> On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote:
> > mysql> select album_id, updated_at, created_at from album_stats group by
> > album_id order by updated_at desc limit 8;
>
> I believe that your problem is that the gro
On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote:
mysql> select album_id, updated_at, created_at from album_stats group by
album_id order by updated_at desc limit 8;
I believe that your problem is that the group by happens before the
order by. Since you're grouping, the updated_at colum
:04 |
+--+-+-+
8 rows in set (0.09 sec)
The problem is that album_id 81 is occuring two times in the list. So I
thought I should add a "group by" in the query:
mysql> select album_id, updated_at, created_at from album_stats group by
album_id order by
Hi Johan,
On Sun, Dec 19, 2010 at 7:11 PM, Johan De Meersman wrote:
> You can't query the index directly, but if you select only fields that are
> in the index, no table lookups will be performed - this is called a covering
> index.
>
Great.. Thanks for the confirmation.
Regards,
Feris
>
> -
On Sun, Dec 19, 2010 at 3:19 AM, Feris Thia <
milis.datab...@phi-integration.com> wrote:
> Hi Everyone,
>
> Is there a way to query values stored in our index instead of using "group
> by" selection which will produce same results ?
>
You can't query the in
Hi Everyone,
Is there a way to query values stored in our index instead of using "group
by" selection which will produce same results ?
Please advice..
Regards,
Feris
I have tables:
CREATE TABLE `tblNames` (
` IdName` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(60) DEFAULT NULL,
PRIMARY KEY (`IdName`),
) ENGINE=MyISAM
CREATE TABLE `tblStatusy` (
`IdStatus` int(11) NOT NULL AUTO_INCREMENT,
`IdName` int(11) DEFAULT NULL,
`Status`
where geneName in
-- returns all geneNames (symbols) with one unique combination of chrom and
strand
(select geneName from
-- returns all unique combinations of symbol, chrom, and strand
(select distinct geneName, chrom, strand
from refFlat) sub1
group by geneName
having count(*) = 1)
group b
symbols but different
chrom and strand). Could anybody show me how to do it?
select geneName as symbol, name as refSeq, chrom, strand, txStart from
refFlat group by refSeq having count(*)=1;
I think that something like
SELECT name FROM (SELECT name, type_id FROM (SELECT * FROM foods));
works
UBSTR( MIN( CONCAT( LPAD(price,6,0),supplier) ), 7) AS MinSupplier,
LEFT( MIN( CONCAT( LPAD(price,6,0),supplier) ), 6)+0 AS MinPrice,
SUBSTR( MAX( CONCAT( LPAD(price,6,0),supplier) ), 7) AS MaxSupplier,
LEFT( MAX( CONCAT( LPAD(price,6,0),supplier) ), 6)+0 AS MaxPrice
FROM products
GRO
> Date: Wed, 14 Jul 2010 10:25:22 -0400
> Subject: Select w/ group by question
> From: smulle...@gmail.com
> To: mysql@lists.mysql.com
>
> I'm having trouble formulating a query to gather the following data. I can
> do this via a script, but now it is more or
Walmart
> (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm
> not really concerned with which vendor is chosen in the result set here.)
>
>
Try this:
select name, product_type, min(cost) from vendors join products on
vendors.id =
= b.vendor_id group by a.type,
a.vendor_id, b.vendor_name all rows are returned because the
type/vendor_id/vendor_name are unique amongst each row. If you remove the
vendor_id and vendor_name from the group by, you get a single row with the
lowest cost for each product, but the vendor_id
_name from
products a join vendors b on a.vendor_id = b.vendor_id group by a.type,
a.vendor_id, b.vendor_name all rows are returned because the
type/vendor_id/vendor_name are unique amongst each row. If you remove the
vendor_id and vendor_name from the group by, you get a single row with the
lowes
NULL,
`weight` int(10) NOT NULL,
PRIMARY KEY (`tableid1`,`tupleid1`,`tableid2`,`tupleid2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
and I'm running this query (note the 'group by'):
insert into graph (node1, node2, tableid1, tupleid1, tableid2,
tupleid2, weight)
`,`tupleid1`,`tableid2`,`tupleid2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
and I'm running this query (note the 'group by'):
insert into graph (node1, node2, tableid1, tupleid1, tableid2,
tupleid2, weight)
select 0, 0, a.tableid, a.tupleid, b.tableid, b.tupleid, count(
works perfectly, i didn't know you could use multiple columns in the
group by. thanks a bunch!
Michael Dykman wrote:
try this:
select accepted_by, problem_type, count(*) from form
where problem_type is not NULL
AND problem_type != 'Test'
AND accepted_by is not null
group
try this:
select accepted_by, problem_type, count(*) from form
where problem_type is not NULL
AND problem_type != 'Test'
AND accepted_by is not null
group by accepted_by, problem_type
On Wed, Oct 28, 2009 at 12:05 PM, Adam Williams
wrote:
> I've written a helpdesk ticket prob
I've written a helpdesk ticket problem and am working on the statistics
module. I'm having problems with group by. For instance, I want to get
the count of the number of different problem types, by how many were
solved by each person. This is my statement:
mysql> se
) AS thecount,
COUNT( * ) / ( SELECT COUNT( * ) FROM agents ) AS percentage
FROM agents
GROUP BY user_agent_parsed
ORDER BY thecount DESC LIMIT 50;
Second issue, once a day I need to archive the result of the above.
Any suggestions on how to best to do that? I can schedule with cron,
or in
nt: Friday, September 25, 2009 1:28 PM
To: mysql@lists.mysql.com
Subject: Stupid GROUP BY question
It must be too late in the week…
Suppose table Cust has one field, CustID. Suppose table Notes has four fields:
NoteID (unique), CustID, NoteTime, and NoteText.
A customer can have zero or more
stomer.
If all I want is the date, then I can do
SELECT Cust.CustID, MAX(Notes.NoteTime) FROM Cust LEFT JOIN Notes ON
Cust.CustID = Notes.Cust_ID GROUP BY Cust.CustID;
That will work just fine, but now I also want the NoteText associated with the
newest note. Obviously I can'
newest note (if any) for each
customer.
If all I want is the date, then I can do
SELECT Cust.CustID, MAX(Notes.NoteTime) FROM Cust LEFT JOIN Notes ON
Cust.CustID = Notes.Cust_ID GROUP BY Cust.CustID;
That will work just fine, but now I also want the NoteText associated with the
newest
Peter,
I am fairly certain, it's not slow because of the event_id look up but
because of the GROUP BY
Suhail
On Sun, Aug 16, 2009 at 2:56 PM, Peter Brawley
wrote:
> Suhail,
>
> Having problems with this query, any ideas on how to optimize this further?
>
> Did you try writ
6,105689,105909,106173,106311,106459,107118,107320,107662,107970,108155,108379,108418,108618,108779,108960,109506,109691,110067,110469,110698,110806,111201,111286,111641,112174,112375,112568,112656,113094,113248,113344,113449,113561,113909,114170,114322,114432,115059,115146,115244,115541,115689,116305,116405,116762,117148,117296,117389,117504,117779,117945,118285,118447,118571,118752)
G
2,115059,115146,115244,115541,115689,116305,116405,116762,117148,117296,117389,117504,117779,117945,118285,118447,118571,118752)
GROUP BY cache_property_str.name
ORDER BY NULL
LIMIT
Thanks! That did the trick.
-Original Message-
From: Olexandr Melnyk [mailto:omel...@gmail.com]
Sent: Monday, July 20, 2009 10:02 AM
To: mysql@lists.mysql.com
Subject: Re: Group by column and Sum another
select contract , sum(amlp)
from maintenance
group by contract;
On Mon, Jul 20
select contract , sum(amlp)
from maintenance
group by contract;
On Mon, Jul 20, 2009 at 6:50 PM, Hagen wrote:
> I am hoping I can get some help with a query I am trying to construct:
>
> I want to group by a 'contract' column and get the sum of the 'amlp' co
I am hoping I can get some help with a query I am trying to construct:
I want to group by a 'contract' column and get the sum of the 'amlp' column
values associated with each contract. I can do a select and group by on
contract
select contract from maintenance group by contr
Hi,
I am using a technique described here at:
http://forums.mysql.com/read.php?20,227102,227102#msg-227102
To make results Random before applying the GROUP BY method.
The query to my table structure is this:
SELECT r.physicians_id,
(SELECT r1.id FROM physicians_images r1 WHERE
(r.physicians_id
ate < B.DT2
group by week(A.mydate);
Give this one a Try !!!
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
-Original Message-
From: Rolan
) DT2 FROM (SELECT
DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR
HOUR) DT FROM (SELECT 4 DOW, 9 HR) AA) AAA) B
Where A.mydate >= B. DT1_9AM
And A.mydate < B. DT2_9AM
group by week(A.mydate);
Give it a Try !!!
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue
Can someone point a link, or show an example.
basically, i have something like
select week(mydate), count(mystuff) from table group by week(mydate);
however, I need week to start on Wed 9am and end next Wed.
What's the easiest way to accomplish that?
thanks,
andrey
--
MySQL General Ma
;>
>> ++--+--+
>> | game | rank | date |
>> ++--+--+
>> | SPORE | 50 | 20090402 |
>> ++--+--+
>> | SINSOL | 11 | 20090104 |
>> ++--+--+
>> | GTA | 21
+
| SINSOL | 11 | 20090104 |
++--+--+
| GTA| 21 | 20080821 |
++--+--+
| WOW| 15 | 20090106 |
++--+--+
How do I go about it?
The initial 'obvious' solution:
select game,rank,max(date) from gametbl group by game
DOES
vious' solution:
select game,rank,max(date) from gametbl group by game
DOESN'T work because the 'rank' value we get will not
necessarily come from the same row holding the 'max(date)'!!
Instead, you have to sort by date first in a subquery
before applying the GROU
Hello,
I think following query would help you
For Ascending
select cpid,sum(score),team from j group by cpid order by sum(score)
For Descending
select cpid,sum(score),team from j group by cpid order by sum(score) desc
Thanks!
2009/1/7 Phil
> A question on grouping I've never been
|3 | 300 | a | team2 |
| aaa |4 | 100 | b | team2 |
| bbb |5 | 300 | b | team1 |
| ccc |6 | 400 | bbbbb | team1 |
| aaa |7 | 101 | c | team1 |
| bbb |8 | 302 | ccc
aa | team1 |
| bbb |2 | 200 | a | team1 |
| ccc |3 | 300 | a | team2 |
| aaa |4 | 100 | b | team2 |
| bbb |5 | 300 | b | team1 |
| ccc |6 | 400 | b | team1 |
| aaa |
|
> 2008-12-2601:13:58|
> |yard|dog|<110.05429, 22.43897, 43.31427>|
> 2008-12-2601:14:30|
> |yard|cat|<109.83472, 22.45278, 43.29317>|
> 2008-12-2601:14:30|
> |yard|dog|<110.05429, 22.43897,
14:57|
*
What I want to do is SELECT only the most recent dateTime and
critterXYZ for
each critterName and return one row for each unique critterName.
So far I have this SELECT statement:
mysql> SELECT r
is SELECT only the most recent dateTime and critterXYZ for
each critterName and return one row for each unique critterName.
So far I have this SELECT statement:
mysql> SELECT regionName,cr
o do is SELECT only the most recent dateTime and critterXYZ for
each critterName and return one row for each unique critterName.
So far I have this SELECT statement:
mysql> SELECT regionName,critterName,crit
an someone suggest a workaround for this (other than upgrading
MySQL).
Thanks,
Nishi
CREATE TABLE t ( id TINYINT, title VARCHAR(10) );
INSERT INTO t (id, title) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'), (4,
'ddd'), (5, 'eee');
(a) DO @sn := 0; S
).
Thanks,
Nishi
CREATE TABLE t ( id TINYINT, title VARCHAR(10) );
INSERT INTO t (id, title) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'), (4,
'ddd'), (5, 'eee');
(a) DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t G
;aaa'), (2, 'bbb'), (3, 'ccc'), (4,
'ddd'), (5, 'eee');
(a) DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t GROUP BY id
ORDER BY id desc;
| sn | id | title |
+--+--+---+
|1 |5 | eee |
|2 |4 | ddd |
#x27;), (2, 'bbb'), (3, 'ccc'),
(4, 'ddd'), (5, 'eee');
(a) DO @sn := 0;
SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t GROUP BY id ORDER BY id desc;
| sn | id | title |
+--+--+---+
|1 |5 | eee |
|2 |4 | ddd |
|
Hi Nishi,
There was a bug in that version that affected the sort order when
combined with a group by statement
(http://bugs.mysql.com/bug.php?id=32202.) There are a couple of
workarounds but they are bad (e.g. removing primary key!)
I would strongly suggest you upgrade to 5.0.67 or
CREATE TABLE t ( id TINYINT, title VARCHAR(10) );
INSERT INTO t (id, title) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'), (4,
'ddd'), (5, 'eee');
(a) DO @sn := 0;
SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t GROUP BY id ORDER BY id desc;
| sn
Learnt!
On Wed, Oct 15, 2008 at 5:28 PM, philip <[EMAIL PROTECTED]> wrote:
> > Date: Tue, 14 Oct 2008 16:55:11 +0300
> > From: Olexandr Melnyk <[EMAIL PROTECTED]>
> > To: [EMAIL PROTECTED], mysql@lists.mysql.com
> > Subject: Re: Problem with GROUP BY
> >
> Date: Tue, 14 Oct 2008 16:55:11 +0300
> From: Olexandr Melnyk <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED], mysql@lists.mysql.com
> Subject: Re: Problem with GROUP BY
>
> http://jan.kneschke.de/projects/mysql/groupwise-max
>
> 2008/10/14 Peter Brawley <
http://jan.kneschke.de/projects/mysql/groupwise-max
2008/10/14 Peter Brawley <[EMAIL PROTECTED]>
> Philip
>
> mysql> SELECT number, MAX(event), name FROM info GROUP BY number;
>>
>
> For discussion & examples see "Within-group aggregates" at
> ht
Philip
mysql> SELECT number, MAX(event), name FROM info GROUP BY number;
For discussion & examples see "Within-group aggregates" at
http://www.artfulsoftware.com/queries.php.
PB
-
philip wrote:
I created a table with,
CREATE TABLE info (
number INTEGER UN
| 1 | David |
| 72 | 3 | David |
+++---+
The closest I have managed to produce using GROUP BY is,
mysql> SELECT number, MAX(event), name FROM info GROUP BY number;
+++---+
| number | MAX(event) | n
ountry != 'Unit' AND Country
>> != 'United States' AND Country != ' ' AND Country IS NOT NULL ) GROUP
>> BY Country ORDER BY Cnt DESC LIMIT 8
>>
>> that g
' AND
> Country != 'United States'
> AND Country != ' '
> AND Country IS NOT NULL )
> GROUP BY Country
> ORDER BY Cnt
> DESC LIMIT
>
> This is a costly query
I suggest that this is not a well normalized. I suggest that at a
min
I have a query:
SELECT Country, COUNT( Country ) AS Cnt FROM properties WHERE (
Country != 'USA' AND Country != 'US' AND Country != 'Unit' AND Country
!= 'United States' AND Country != ' ' AND Country IS NOT NULL ) GROUP
BY Country ORDER BY Cnt
able2
WHERE table1.some_field = 'value'
AND table2.id1 = table1.id
GROUP BY table1.id
This works fine and returns each record in table1, grouped by id,
with the sum of scores from table2. However, when I do this query:
SELECT table1.id,SUM(table2.score) as table2_score, SUM(ta
Off the top of my head I can't think of a way of getting the output in
the format that you want.
If you use a query like:
SELECT SUBSTRING(sales_date,1,10), sales_type, COUNT(sales_id)
FROM sales_activity
GROUP BY SUBSTRING(sales_date,1,10), sales_type;
You'll get output l
On Sat, Jun 21, 2008 at 8:34 AM, Joe Pearl <[EMAIL PROTECTED]> wrote:
> Thanks, but this is not the result I want.
I really think it is. You seem to be misunderstanding how GROUP BY
works. In any other database than MySQL, the SQL you wrote would
simply give an error. In MySQL, it
d out what the max date is, and then find
out what record that max date belongs to so you can pull the rest of
the information from that record. You can do this by use a select on a
"virtual" table.
First, get the max date:
select name,max(acq_date) AS mx_acq_date from cust_full grou
Thanks, but this is not the result I want. The SQL for the correct result
is at
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group.html and
I'm apparently using the correct SQL.
The problem is that the columns are not coming out correctly - the rows
considered for the max functio
On Fri, Jun 20, 2008 at 10:50 PM, Joe Pearl <[EMAIL PROTECTED]> wrote:
> I want to get back only the most recent entry for each person and I don't
> care about the order. I want the result to show Jim with the acq_date of
> "2008-01-03", Mary and Sally with the location and date for all of them.
item_id, location, max(acq_date) from cust_full
group by name;
+---+-+--+---+
| name | item_id | location | max(acq_date) |
+---+-+--+---+
| Jim | 1 | OH | 2008-01-03|
| John | 0 | | -00
I happen to have worked on a similar query this morning, so it's in my
mind :)
SELECT SUBSTRING(sales_date,1,10), COUNT(sales_id)
FROM sales_activity
WHERE sales_type = 1
GROUP BY SUBSTRING(sales_date,1,10);
should do the trick.
On Tue, 2008-06-17 at 18:21 -0700, Grant Giddens wrote:
Re: Do I need to use GROUP BY to do this?
yes
Grant Giddens schrieb:
Hi,
I have a table where I keep sales transactions, so I'm trying to do a
query that will count the number of transactions per day.
My test data looks like:
What type of query do I need to get that inform
Hi,
I have a table where I keep sales transactions, so I'm trying to do a
query that will count the number of transactions per day.
My test data looks like:
--
-- Table structure for table `sales_activity`
--
CREATE TABLE `sales_activity` (
`sales_id` int(11) NOT NULL auto_increment,
`
table1.some_field = 'value'
AND table2.id1 = table1.id
GROUP BY table1.id
This works fine and returns each record in table1, grouped by id,
with the sum of scores from table2. However, when I do this query:
SELECT table1.id,SUM(table2.score) as table2_score, SUM(table3.score)
as table3_s
1 - 100 of 808 matches
Mail list logo
