List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:August 29 2000 12:17am
Subject:Re: help with group by
View as plain text  
At 9:10 PM +0100 08-28-2000, Levy Carneiro Jr. wrote:
>Hi there,
>
>I got a MySQL table like this:
>
>code		date		value
>1		20000102	20.00
>2		20000109	12.00
>3		20000114	23.00
>4		20000201	11.00
>5		20000202	10.00
>6		20000224	12.00
>7		20000311	25.00
>8		20000318	30.00
>9		20000323	13.00
>10		20000329	19.00
>
>SELECT	SUM(value)
>FROM	table
>GROUP BY	XXXXXX
>
>I'd like to run a query that returns how many rows as the number of 
>months in "date". In each row, the sum for the period.

What do you mean by 'the number of months in "date"'?

Also, "how many rows" is different than "the sum".  I'm not able to
understand quite what you want.  If what you want is the sum of the
value column, per each month of each year, see below.

>I'd have to use something like a substring function, but "group by" 
>doesn't accept that. Does anyone know how to solve this specific 
>query ? Is this possible ?

Yes; try using column aliases and naming those in the GROUP BY clasue.
Here's an example:

SELECT SUM(value), YEAR(date) AS year, MONTH(date) AS month
FROM table
GROUP BY year, month

Is that what you want?

-- 
Paul DuBois, paul@stripped
Thread
help with group byLevy Carneiro Jr.29 Aug
  • Re: help with group byPaul DuBois29 Aug
    • Re: help with group byLevy Carneiro Jr.29 Aug