Re: [sqlite] column totals
Thank You Works well summing the columns but I don't get a 'tot' label I, get 1 54 3 2 26 4 3 56 8 0136 15 On 22 February 2013 23:20, Clemens Ladisch clem...@ladisch.de wrote: Paul Sanderson wrote: SELECT cat, COUNT(*) AS occ, COUNT(DISTINCT tes) AS uni, COUNT(tag) AS tagged FROM rtable WHERE qu 0 AND qu 4 GROUP BY qu The table would look something like 1 54 3 2 26 4 3 56 8 I want to modify the above sql query to sum the second and third columns and get a resultant table something like 1 54 3 2 26 4 3 56 8 tot 136 15 Use UNION to add a second subquery without grouping: SELECT ...your query... UNION ALL SELECT 'tot', COUNT(*), COUNT(DISTINCT tes), COUNT(tag) FROM table WHERE qu BETWEEN 1 AND 3 Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about aggregate returning empty row
Ok, thanks ! There are certainly good reasons for that, but I find it surprising at first sight. Pierre That's SQL standard -- query with aggregate functions always return at least one row. [tested under 3.6.12 and 3.7.15.2] I have a question regarding the use of aggregate functions. Let's imagine the following db : create table A (id1 INTEGER UNIQUE PRIMARY KEY, id2 INTEGER); It is empty. The following query : select id1,id2 from A; returns nothing, there is no row. However, the following query, using the aggregate min() : select min(id1),id2 from A; returns an empty line (displays '|' in the shell). Using avg(), max()... will do the same. With the C interface, SQLITE_ROW is returned, and I must test sqlite_column_type() against SQLITE_NULL to check that in fact, there is no result. Is this expected behaviour ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column totals
Paul Sanderson wrote: Works well summing the columns but I don't get a 'tot' label I, get 1 54 3 2 26 4 3 56 8 0136 15 Works for me. Your example data and your query don't match (three vs. four columns). Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column totals
On Feb 23, 2013, at 12:07 AM, Paul Sanderson sandersonforens...@gmail.com wrote: any ideas? As mentioned, you will need two queries and union their respective result sets. For example, assuming the following data set: create table test( key, value ); insert intotest ( key, value ) values ( 'a', 1 ), ( 'a', 2 ), ( 'a', null ), ( 'b', 1 ), ( 'b', 1 ), ( 'b', 2 ); First, get the summary per key: selectkey, count( * ) as count, count( distinct value ) as count_distinct, count( value ) as count_value from test group by key Then union it all with the grand total: union all select'total' as key, sum( count ) as count, sum( count_distinct ) as count_distinct, sum( count_value ) as count_value from ( selectkey, count( * ) as count, count( distinct value ) as count_distinct, count( value ) as count_value from test group by key ) Resulting in: key|count|count_distinct|count_value a|3|2|2 b|3|2|3 total|6|4|5 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about aggregate returning empty row
Hi, you may add a dummy GROUP BY clause, to return no row if the table is empty: select min (x) from tbl group by null; By the way, the good reasons for the standard behaviour may be the use of other aggregate functions like COUNT (*). Op 23 feb 2013, om 11:51 heeft Pierre Chatelier het volgende geschreven: Ok, thanks ! There are certainly good reasons for that, but I find it surprising at first sight. Pierre That's SQL standard -- query with aggregate functions always return at least one row. [tested under 3.6.12 and 3.7.15.2] I have a question regarding the use of aggregate functions. Let's imagine the following db : create table A (id1 INTEGER UNIQUE PRIMARY KEY, id2 INTEGER); It is empty. The following query : select id1,id2 from A; returns nothing, there is no row. However, the following query, using the aggregate min() : select min(id1),id2 from A; returns an empty line (displays '|' in the shell). Using avg(), max()... will do the same. With the C interface, SQLITE_ROW is returned, and I must test sqlite_column_type() against SQLITE_NULL to check that in fact, there is no result. Is this expected behaviour ? ___ 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] column totals
Hmm works OK at an sqlite prompt but not when I pass the query though a data access component On 23 February 2013 11:05, Clemens Ladisch clem...@ladisch.de wrote: Paul Sanderson wrote: Works well summing the columns but I don't get a 'tot' label I, get 1 54 3 2 26 4 3 56 8 0136 15 Works for me. Your example data and your query don't match (three vs. four columns). Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite.dll ?
Good day I have a question, I use QT and c++ for coding and the driver sqlite, but I hear about System.Data.SQLite.dll that is better than only sqlite. Someone can give me information about System.Data.SQLite.dll and the advantages over sqlite.dll and if can I use it with QT creator?. best regards Ing. Yoelvis Mulen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column totals
On 23 Feb 2013, at 12:54pm, Paul Sanderson sandersonforens...@gmail.com wrote: Hmm works OK at an sqlite prompt but not when I pass the query though a data access component The sqlite shell tool formats output for you, formatting to make the column layout look good and adding headers according to how you have it set. The SQLite API just returns the raw data you requested and it's up to the software to do formatting and layout. If you're using a data access component then you're obviously doing programming, so you can write your own code to calculate totals and add whatever headers you want. Sooner or later you're going to have to do some programming, it might as well be now. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite.dll ?
Am 22.02.2013 22:13, schrieb Yoelvis: Good day I have a question, I use QT and c++ for coding and the driver sqlite, but I hear about System.Data.SQLite.dll that is better than only sqlite. Someone can give me information about System.Data.SQLite.dll and the advantages over sqlite.dll and if can I use it with QT creator?. best regards Ing. Yoelvis Mulen System.Data.SQLite is the .NET wrapper for SQLite, maintained by the SQLite team. Very easy to use from a language targeting the CLR. As you're coding in C++, it's unusable for you. And it's certainly not better than SQLite in C - it's just a wrapper. Best regards, Bernd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Thread sync issue
Hi All, I am facing synchronisation issues in my module which uses Sqlite APIs and accessed by many threads. Do I need to protect sqlite APIs with mutex ? Sqlite doc says it thread safe ... Thanks Regards, Ashok - Reply message - From: Bernd be...@web.de To: sqlite-users@sqlite.org sqlite-users@sqlite.org Subject: [sqlite] System.Data.SQLite.dll ? Date: Sun, Feb 24, 2013 12:59 am __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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
