detail and grouping

> I would like to select and list all the transactions within date
> limits, _and_ print the totals grouped by provider. If I use the
> group by clause, all I get is one transaction amount and the total,
> both are correct but not what I want.
> eg:
> provider date amount total
> bill 2010-05-01 10
> bill 2010-05-02 20
> bill 30
>
> george 2010-05-02 10
> george 2010=05-05 10
> george 20
>
> I don't know if this is possible.


Not sure what you mean.

If you want to group and get totals, then use SUM(coulmname)

SELECT provider, date, SUM(amount) AS amount, total FROM ... GROUP BY provider


If you want to get all the rows for a provider, then use ORDER BY,

SELECT provider, date, amount, total FROM ... ORDER BY provider


If you want to have both, then you would need to use nested SELECT with an UNION.

Or you can make it simple and do the summing up in the script/progeaming
language you are using to connect to the mysql.

--

//Aho
 >> Stay informed about: detail and grouping 

On May 9, 3:55 pm, The Natural Philosopher
wrote:
> J.O. Aho wrote:
> >> I would like to select and list all the transactions within date
> >> limits, _and_ print the totals grouped by provider.  If I use the
> >> group by clause, all I get is one transaction amount and the total,
> >> both are correct but not what I want.
> >> eg:
> >> provider   date           amount  total
> >> bill           2010-05-01      10
> >> bill           2010-05-02      20
> >> bill                                         30
>
> >> george 2010-05-02        10
> >> george 2010=05-05       10
> >> george                                   20
>
> >> I don't know if this is possible.
>
> > Not sure what you mean.
>
> > If you want to group and get totals, then use SUM(coulmname)
>
> > SELECT provider, date, SUM(amount) AS amount, total FROM ... GROUP BY provider
>
> > If you want to get all the rows for a provider, then use ORDER BY,
>
> > SELECT provider, date, amount, total FROM ... ORDER BY provider
>
> > If you want to have both, then you would need to use nested SELECT with an UNION.
>
> > Or you can make it simple and do the summing up in the script/progeaming
> > language you are using to connect to the mysql.
>
> the latter is usually easier.
>
> Its nice to have SQL do all the work, but in reality an SQL query is a
> single result set from the data: what you need is two results.
>
> Either two separate enquires, or build the second result from an
> iteration through the first result.

Or "in reality" use the solution that I posted!!!

SELECT
provider,
date,
SUM(amount)
FROM test.transactions t
GROUP BY provider, date WITH ROLLUP

Gives:
provider date SUM(amount)
bill 2010-05-01 10
bill 2010-05-02 20
bill 30
george 2010-05-02 10
george 2010-05-05 10
george 20
50
 >> Stay informed about: detail and grouping 

J.O. Aho wrote:
>> I would like to select and list all the transactions within date
>> limits, _and_ print the totals grouped by provider. If I use the
>> group by clause, all I get is one transaction amount and the total,
>> both are correct but not what I want.
>> eg:
>> provider date amount total
>> bill 2010-05-01 10
>> bill 2010-05-02 20
>> bill 30
>>
>> george 2010-05-02 10
>> george 2010=05-05 10
>> george 20
>>
>> I don't know if this is possible.
>
>
> Not sure what you mean.
>
> If you want to group and get totals, then use SUM(coulmname)
>
> SELECT provider, date, SUM(amount) AS amount, total FROM ... GROUP BY provider
>
>
> If you want to get all the rows for a provider, then use ORDER BY,
>
> SELECT provider, date, amount, total FROM ... ORDER BY provider
>
>
> If you want to have both, then you would need to use nested SELECT with an UNION.
>
> Or you can make it simple and do the summing up in the script/progeaming
> language you are using to connect to the mysql.
>
the latter is usually easier.

Its nice to have SQL do all the work, but in reality an SQL query is a
single result set from the data: what you need is two results.

Either two separate enquires, or build the second result from an
iteration through the first result.
 >> Stay informed about: detail and grouping 

Captain Paralytic wrote:
> On May 9, 3:55 pm, The Natural Philosopher
> wrote:
>> J.O. Aho wrote:
>>>> I would like to select and list all the transactions within date
>>>> limits, _and_ print the totals grouped by provider. If I use the
>>>> group by clause, all I get is one transaction amount and the total,
>>>> both are correct but not what I want.
>>>> eg:
>>>> provider date amount total
>>>> bill 2010-05-01 10
>>>> bill 2010-05-02 20
>>>> bill 30
>>>> george 2010-05-02 10
>>>> george 2010=05-05 10
>>>> george 20
>>>> I don't know if this is possible.
>>> Not sure what you mean.
>>> If you want to group and get totals, then use SUM(coulmname)
>>> SELECT provider, date, SUM(amount) AS amount, total FROM ... GROUP BY provider
>>> If you want to get all the rows for a provider, then use ORDER BY,
>>> SELECT provider, date, amount, total FROM ... ORDER BY provider
>>> If you want to have both, then you would need to use nested SELECT with an UNION.
>>> Or you can make it simple and do the summing up in the script/progeaming
>>> language you are using to connect to the mysql.
>> the latter is usually easier.
>>
>> Its nice to have SQL do all the work, but in reality an SQL query is a
>> single result set from the data: what you need is two results.
>>
>> Either two separate enquires, or build the second result from an
>> iteration through the first result.
>
> Or "in reality" use the solution that I posted!!!
>
> SELECT
> provider,
> date,
> SUM(amount)
> FROM test.transactions t
> GROUP BY provider, date WITH ROLLUP
>
> Gives:
> provider date SUM(amount)
> bill 2010-05-01 10
> bill 2010-05-02 20
> bill 30
> george 2010-05-02 10
> george 2010-05-05 10
> george 20
> 50


wasn't aware of ROLLUP. Will have to look that up..
 >> Stay informed about: detail and grouping 

On May 9, 9:31 am, Captain Paralytic wrote:
> On May 9, 11:30 am, bill wrote:
>
> > I would like to select and list all the transactions within date
> > limits, _and_ print the totals grouped by provider.  If I use the
> > group by clause, all I get is one transaction amount and the total,
> > both are correct but not what I want.
> > eg:
> > provider   date           amount  total
> > bill           2010-05-01      10
> > bill           2010-05-02      20
> > bill                                         30
>
> > george 2010-05-02        10
> > george 2010=05-05       10
> > george                                   20
>
> > I don't know if this is possible.
>
> You were nearly there with your subject. If you had put
> detail and total
> and searched this group, you would have dome across:http://groups.google.com/group/comp.databases.mysql/browse_frm/thread...
>
> The last post in that thread from me will point you to the MySQL GROUP
> BY Modifier of ROLLUP, which is just what you are after.

lovely - thanks
I know I can do it in PHP, but this was an attempt to learn more
MySQL, which both I and NP did.
 >> Stay informed about: detail and grouping