Re: [sqlite] Subtotal SQL

[John Machin]

On 29/07/2009 11:34 PM, Adler, Eliedaat wrote:
> SQL/sqlite challenge  for all:

It would be helpful if you made it plain whether you are asking a trick
question, or are a novice with a perceived problem (and whether the
management is insisting that you absolutely must have an SQL-only
solution irrespective of overall efficiency and understandability).


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subtotal SQL

[Igor Tandetnik]

Adler, Eliedaat  wrote:
> I have a table with many columns describing objects on a disk.
>
>filename, date, owner, size
>A12MO100
>B13   JAK90
>C10   MO80
>A13   LU  70
>
> I have many different ways of ordering these objects. Usually using
> several sort parameters.
>
> I need a running sum of size that works regardless of what order the
> objects are in.
>
> Sort by date,owner
> Name SizeSum
> C80   80
> A  100  180
> B   90   270
> A  70  340

You'll be better off calculating the running total in the host 
application, as you step over the resultset. But, if you insist:

select filename, size,
(select sum(t2.size) from mytable t2
 where t2.date <= t1.date and (t2.date < t1.date or t2.owner < 
t1.owner) Sum
from mytable t1
order by date, owner;

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subtotal SQL

[P Kishor]

On Wed, Jul 29, 2009 at 8:34 AM, Adler, Eliedaat wrote:
>
> SQL/sqlite challenge  for all:
>
>
> I have a table with many columns describing objects on a disk.
>
>    filename, date, owner, size
>    A            12    MO    100
>    B            13   JAK    90
>    C            10   MO    80
>    A            13   LU      70
>
> I have many different ways of ordering these objects. Usually using several 
> sort parameters.
>
> I need a running sum of size that works regardless of what order the objects 
> are in.
>
> Sort by date,owner
> Name     Size    Sum
> C            80       80
> A          100      180
> B           90       270
> A          70      340
>
>
> Sort by filename, owner
> Name     Size     Sum
> A           70        70
> A          100      170
> B           90       260
> C          80        340
>
> User Function/Aggregates welcome!
> thanks
> Eliedaat Adler
>
>


sqlite> CREATE TABLE a (a_id INTEGER PRIMARY KEY, a_desc TEXT, a_tot INTEGER);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('bla', 20);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('foo', 30);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('bar', 13);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('baz', 132);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('qux', 42);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('gam', 12);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('fro', 87);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('nic', 119);
sqlite> .h on
sqlite> .m col
sqlite> SELECT * FROM a;
a_ida_desc  a_tot
--  --  --
1   bla 20
2   foo 30
3   bar 13
4   baz 132
5   qux 42
6   gam 12
7   fro 87
8   nic 119
sqlite> SELECT a1.a_desc, a1.a_tot, SUM(a2.a_tot) total
   ...> FROM a a1, a a2
   ...> WHERE a1.a_tot <= a2.a_tot OR
   ...>   (a1.a_desc=a2.a_desc AND a1.a_tot = a2.a_tot)
   ...> GROUP BY a1.a_desc, a1.a_tot
   ...> ORDER BY a1.a_tot DESC, a1.a_desc DESC;
a_desc  a_tot   total
--  --  --
baz 132 132
nic 119 251
fro 87  338
qux 42  380
foo 30  410
bla 20  430
bar 13  443
gam 12  455
sqlite>


-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subtotal SQL

[Swithun Crowe]

Hello

AE I have many different ways of ordering these objects. Usually using 
AE several sort parameters. I need a running sum of size that works 
AE regardless of what order the objects are in.

I tried creating a UDF, but it seems that ordering is done after the 
unordered results have been collected, so the running total column was not 
increasing every time, as it should.

There probably is a sub query that would give you a sum of the rows up to 
and including each row in the table, depending on different orderings. But 
it would be quite slow if you had a lot of rows.

Then I thought of using the UDF on a sorted sub query, e.g.

SELECT my_sum(t2.val)
  FROM (SELECT *
  FROM table AS t1
  ORDER BY t1.col1) AS t2
ORDER BY t2.col1;

where 'col1' is what ever column you want to sort on, and val is the 
column to be summed.

Without the outer order by clause, the results were wrong. But now the 
table is only sorted twice, rather than for each row in the table.

The UDF is just a function which has access to a variable using the 
sqlite3_user_data function. The value of the column being summed is added 
to an integer in this variable (a C struct), and then this new value is 
returned using sqlite3_result_int.

It looks like this:

/* function */
void my_sum(sqlite3_context *ctxt, int argc, sqlite3_value **argv)
{  
   aggregate_ctxt *agg_ctxt;
   agg_ctxt = (aggregate_ctxt *) sqlite3_user_data(ctxt);
   agg_ctxt->total += sqlite3_value_int(argv[0]);
   
   sqlite3_result_int(ctxt, agg_ctxt->total);
}

But this would be more easily done once the ordered results were in a 2D 
array, i.e. outside of SQL/sqlite.

Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subtotal SQL

[Beau Wilkinson]

I think this may involve a subquery, probably in the SELECT list itself, which 
uses ROWID (or ROWNUMBER... I can't remember which one is a part of the SQL 
standard rather than proprietary).


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of cmar...@unc.edu [cmar...@unc.edu]
Sent: Wednesday, July 29, 2009 9:08 AM
To: punk...@eidesis.org; General Discussion of SQLite Database
Subject: Re: [sqlite] Subtotal SQL

On Wed, 29 Jul 2009, P Kishor wrote:

> For my part, I don't know how to do a running total in a result set
> unless I have some kind of a counter that keeps track of the "row
> before the current row"

This is also the only way I know of.

Chris


> On Wed, Jul 29, 2009 at 8:50 AM, Rich Shepard<rshep...@appl-ecosys.com> wrote:
>> On Wed, 29 Jul 2009, Adler, Eliedaat wrote:
>>
>>> SQL/sqlite challenge  for all:
>>
>>   No challenge for anyone who knows SQL.
>>
>>> I need a running sum of size that works regardless of what order the 
>>> objects are in.
>>> User Function/Aggregates welcome!
>>
>>   _All_ implementations of SQL include a suite of aggregate functions, and
>> SUM() is among those. Take a look at the SQLite Web site, Mike Owens's book,
>> Rick van der Lans's book, or any introduction to SQL.
>>
>>   You will write, "SELECT filename, date, owner, size, SUM(size) AS total
>>FROM mytable;"
>>
>
>
> Well, not really. The above totals across the entire result set. The
> OP wants a running total, kinda like in a spreadsheet.
>
> Since the question is phrased as a "challenge," I wonder if the OP has
> the answer already, and merely wants to test the SQL sages on the
> list. Otherwise, a better spirit would be to simply ask the question
> as a question, and not as a challenge.
>
> For my part, I don't know how to do a running total in a result set
> unless I have some kind of a counter that keeps track of the "row
> before the current row"
>
>
>
>
>
> --
> Puneet Kishor
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subtotal SQL

[cmartin]

On Wed, 29 Jul 2009, P Kishor wrote:


For my part, I don't know how to do a running total in a result set
unless I have some kind of a counter that keeps track of the "row
before the current row"


This is also the only way I know of.

Chris



On Wed, Jul 29, 2009 at 8:50 AM, Rich Shepard wrote:

On Wed, 29 Jul 2009, Adler, Eliedaat wrote:


SQL/sqlite challenge  for all:


  No challenge for anyone who knows SQL.


I need a running sum of size that works regardless of what order the objects 
are in.
User Function/Aggregates welcome!


  _All_ implementations of SQL include a suite of aggregate functions, and
SUM() is among those. Take a look at the SQLite Web site, Mike Owens's book,
Rick van der Lans's book, or any introduction to SQL.

  You will write, "SELECT filename, date, owner, size, SUM(size) AS total
                       FROM mytable;"




Well, not really. The above totals across the entire result set. The
OP wants a running total, kinda like in a spreadsheet.

Since the question is phrased as a "challenge," I wonder if the OP has
the answer already, and merely wants to test the SQL sages on the
list. Otherwise, a better spirit would be to simply ask the question
as a question, and not as a challenge.

For my part, I don't know how to do a running total in a result set
unless I have some kind of a counter that keeps track of the "row
before the current row"





--
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subtotal SQL

[P Kishor]

On Wed, Jul 29, 2009 at 8:50 AM, Rich Shepard wrote:
> On Wed, 29 Jul 2009, Adler, Eliedaat wrote:
>
>> SQL/sqlite challenge  for all:
>
>   No challenge for anyone who knows SQL.
>
>> I need a running sum of size that works regardless of what order the objects 
>> are in.
>> User Function/Aggregates welcome!
>
>   _All_ implementations of SQL include a suite of aggregate functions, and
> SUM() is among those. Take a look at the SQLite Web site, Mike Owens's book,
> Rick van der Lans's book, or any introduction to SQL.
>
>   You will write, "SELECT filename, date, owner, size, SUM(size) AS total
>                        FROM mytable;"
>


Well, not really. The above totals across the entire result set. The
OP wants a running total, kinda like in a spreadsheet.

Since the question is phrased as a "challenge," I wonder if the OP has
the answer already, and merely wants to test the SQL sages on the
list. Otherwise, a better spirit would be to simply ask the question
as a question, and not as a challenge.

For my part, I don't know how to do a running total in a result set
unless I have some kind of a counter that keeps track of the "row
before the current row"





-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subtotal SQL

[Rich Shepard]

On Wed, 29 Jul 2009, Adler, Eliedaat wrote:

> SQL/sqlite challenge  for all:

   No challenge for anyone who knows SQL.

> I need a running sum of size that works regardless of what order the objects 
> are in.
> User Function/Aggregates welcome!

   _All_ implementations of SQL include a suite of aggregate functions, and
SUM() is among those. Take a look at the SQLite Web site, Mike Owens's book,
Rick van der Lans's book, or any introduction to SQL.

   You will write, "SELECT filename, date, owner, size, SUM(size) AS total
FROM mytable;"

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Subtotal SQL

[Adler, Eliedaat]

SQL/sqlite challenge  for all:


I have a table with many columns describing objects on a disk.

filename, date, owner, size
A12MO100
B13   JAK90
C10   MO80
A13   LU  70

I have many different ways of ordering these objects. Usually using several 
sort parameters.

I need a running sum of size that works regardless of what order the objects 
are in.

Sort by date,owner
Name SizeSum
C80   80
A  100  180
B   90   270
A  70  340


Sort by filename, owner
Name Size Sum
A   7070
A  100  170
B   90   260
C  80340

User Function/Aggregates welcome!
thanks
Eliedaat Adler



This message is confidential and intended only for the addressee. If you have 
received this message in error, please immediately notify the 
postmas...@nds.com and delete it from your system as well as any copies. The 
content of e-mails as well as traffic data may be monitored by NDS for 
employment and security purposes.
To protect the environment please do not print this e-mail unless necessary.

An NDS Group Limited company. www.nds.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users