Re: [sqlite] Group-by and order-by-desc does not work as expected

On 19 Sep 2019, at 1:14pm, Fredrik Larsen wrote: > I have a aggregate query that works as expected when the ordering is > ascending, but uses a TMP B-TREE when changing order to descending, see > stackoverflow link below. For experimental purposes, you might take a backup copy of your database

[sqlite] Group-by and order-by-desc does not work as expected

/questions/58009898/sqlite-group-by-with-sort-by-desc-does-not-work-as-expected Fredrik Larsen ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP BY and ICU collation

qlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] GROUP BY and ICU collation Hi Keith, thanks for your response (which partly goes beyond my understanding, but I'm learning :-)). But you are not using the same "expression" for selecting, sorting, and grouping. That is, you

Re: [sqlite] GROUP BY and ICU collation

gt; --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Michael Herger >Sent: Friday, 8 February, 2

Re: [sqlite] GROUP BY and ICU collation

On 2/8/19 9:11 AM, David Raymond wrote: > Remember that fancy collations don't just look at 1 character at a time, they > look at the whole thing, and can do surprising stuff based on that. In this > case the order of preference for the collation looks like "when it's part of > a larger word,

Re: [sqlite] GROUP BY and ICU collation

ginal Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Michael Herger Sent: Friday, February 08, 2019 4:13 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] GROUP BY and ICU collation Hi Keith, thanks for your response (which partly

Re: [sqlite] GROUP BY and ICU collation

te de_DE,1,1) and the function de_DE would have to transmorgificate its value to the result you want to see. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. -Original Message- From: sqlite-users [mailto:sqlite-use

Re: [sqlite] GROUP BY and ICU collation

nly a Stairway to >Heaven says a lot about anticipated traffic volume. >> >>> -Original Message- >>> From: sqlite-users [mailto:sqlite-users- >>> boun...@mailinglists.sqlite.org] On Behalf Of li...@herger.net >>> Sent: Thursday, 7 February, 201

Re: [sqlite] GROUP BY and ICU collation

On 8 Feb 2019, at 5:47am, li...@herger.net wrote: > In sorting it seems to consider Š "the same" as S, but it doesn't in > grouping. That may be a bug. Can you construct a small test database, where your data features, say, R, S, T, and Š, and show how ORDER BY behaves differently to GROUP

Re: [sqlite] GROUP BY and ICU collation

Original Message- From: sqlite-users [mailto:sqlite-users- boun...@mailinglists.sqlite.org] On Behalf Of li...@herger.net Sent: Thursday, 7 February, 2019 05:12 To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] GROUP BY and ICU collation Hi there, I'm trying to create a list with an index

Re: [sqlite] GROUP BY and ICU collation

te-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of li...@herger.net >Sent: Thursday, 7 February, 2019 05:12 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] GROUP BY and ICU collation > >>> Hi there, >>> >>> I'm trying to create

Re: [sqlite] GROUP BY and ICU collation

On 7 Feb 2019, at 5:02pm, li...@herger.net wrote: >> If you don't want to have to keep specifying the COLLATE, put it in the >> table definition: >> CREATE TABLE ... ( ... artist TEXT COLLATE de_DE ...) > > Thanks for the hint. But the application is localized to the user's language. >

Re: [sqlite] GROUP BY and ICU collation

TBH: I didn't even know about this. I thought the COLLATE at the end of the statement would do it for all. If you don't want to have to keep specifying the COLLATE, put it in the table definition: CREATE TABLE ... ( ... artist TEXT COLLATE de_DE ...) Thanks for the hint. But the

Re: [sqlite] GROUP BY and ICU collation

On 7 Feb 2019, at 12:12pm, li...@herger.net wrote: > TBH: I didn't even know about this. I thought the COLLATE at the end of the > statement would do it for all. If you don't want to have to keep specifying the COLLATE, put it in the table definition: CREATE TABLE ... ( ... artist TEXT

[sqlite] GROUP BY and ICU collation

Hi there, I'm trying to create a list with an index list. Eg. I have artists: Sting Šuma Čovjek Suzanne Vega That's the sort order I'd get using an ICU collation. "Šuma Čovjek" would be sorted as "Suma..." as expected. Now I'd like to create an index bar by providing groups of the first

Re: [sqlite] GROUP BY and ICU collation

On 2/6/19, li...@herger.net wrote: > Hi there, > > I'm trying to create a list with an index list. Eg. I have artists: > > Sting > Šuma Čovjek > Suzanne Vega > > That's the sort order I'd get using an ICU collation. "Šuma Čovjek" > would be sorted as "Suma..." as expected. > > Now I'd like to

[sqlite] GROUP BY and ICU collation

Hi there, I'm trying to create a list with an index list. Eg. I have artists: Sting Šuma Čovjek Suzanne Vega That's the sort order I'd get using an ICU collation. "Šuma Čovjek" would be sorted as "Suma..." as expected. Now I'd like to create an index bar by providing groups of the first

Re: [sqlite] Group by counting 2 different sub-groups

Ah, yes, of course. Thanks. RBS On Sun, Nov 25, 2018 at 12:24 PM R Smith wrote: > > On 2018/11/25 1:50 PM, Bart Smissaert wrote: > > Is it possible to use the aliases diab_count and drug_count directly in a > > fourth column to show the percentage? > > No. > > > This doesn't work: > > > >

Re: [sqlite] Group by counting 2 different sub-groups

On 2018/11/25 1:50 PM, Bart Smissaert wrote: Is it possible to use the aliases diab_count and drug_count directly in a fourth column to show the percentage? No. This doesn't work: select gp_name, sum(emis_number in (select emis_number from diabetics)) as diab_count, sum(emis_number in

Re: [sqlite] Group by counting 2 different sub-groups

After adding 2 other views it looks better: select gp_name, sum(emis_number in (select emis_number from diabetics)) as diab_count, sum(emis_number in (select emis_number from diab_on_non_insulin) or emis_number in (select emis_number from diab_on_insulin)) as drug_count from patients group by

Re: [sqlite] Group by counting 2 different sub-groups

Yes, thanks, got this worked out now. Had to make 2 alterations: 1. missing closing bracket after from diabetic) 2. needed to make sure that the grouped drug counts were only in patients with diabetes So, this works: select gp_name, sum(emis_number in (select emis_number from diabetics)) as

Re: [sqlite] Group by counting 2 different sub-groups

On 11/24/2018 8:51 PM, Bart Smissaert wrote: Ok, in the first count column I would like the grouped counts for patients in the views on_non_insulin or on_insulin and in the second count column I would like the grouped counts for patients the view diabetics. Well, you already know the technique

Re: [sqlite] Group by counting 2 different sub-groups

So, in other words in the second count column I would like the result of this: select p.gp_name as GP, count(d.emis_number) as pat_count from patients p inner join diabetics d on(p.emis_number = d.emis_number) group by GP order by pat_count asc RBS On Sun, 25 Nov 2018, 01:51 Bart Smissaert Ok,

Re: [sqlite] Group by counting 2 different sub-groups

Ok, in the first count column I would like the grouped counts for patients in the views on_non_insulin or on_insulin and in the second count column I would like the grouped counts for patients the view diabetics. Diabetics holds the largest number of ID and the ID's in on_non_insulin and

Re: [sqlite] Group by counting 2 different sub-groups

On 11/24/2018 7:59 PM, Bart Smissaert wrote: Thanks, was aware, but the SQL was indeed wrong as posted and should have brackets around the 2 or conditions. In this case, as far as I can tell you should end up with diab_count equal to pat_count, since "emis_number in(select emis_number from

Re: [sqlite] Group by counting 2 different sub-groups

OK, will describe the data as done before. RBS On Sun, Nov 25, 2018 at 1:08 AM Simon Slavin wrote: > On 25 Nov 2018, at 12:59am, Bart Smissaert > wrote: > > > Could I post a little demo SQLite file? Not sure now if this is allowed > as an attachment. > > This mailing list strips attachments.

Re: [sqlite] Group by counting 2 different sub-groups

On 25 Nov 2018, at 12:59am, Bart Smissaert wrote: > Could I post a little demo SQLite file? Not sure now if this is allowed as an > attachment. This mailing list strips attachments. You could use the SQLite CLI tool to .dump the database as a text file, and paste it into a message. If you

Re: [sqlite] Group by counting 2 different sub-groups

Thanks, was aware, but the SQL was indeed wrong as posted and should have brackets around the 2 or conditions. Corrected now. Could I post a little demo SQLite file? Not sure now if this is allowed as an attachment. That would be easiest. RBS On Sun, Nov 25, 2018 at 12:52 AM Igor Tandetnik

Re: [sqlite] Group by counting 2 different sub-groups

On 11/24/2018 7:44 PM, Bart Smissaert wrote: The very much simplified example works fine, but my real SQL is a bit more complex: select gp_name, count(*) as pat_count, sum(emis_number in(select emis_number from diabetics)) as diab_count from patients where emis_number in(select emis_number from

Re: [sqlite] Group by counting 2 different sub-groups

The very much simplified example works fine, but my real SQL is a bit more complex: select gp_name, count(*) as pat_count, sum(emis_number in(select emis_number from diabetics)) as diab_count from patients where emis_number in(select emis_number from diabetics) and emis_number in(select

Re: [sqlite] Group by counting 2 different sub-groups

Thanks for that! Very nice and simple. (note there is a superfluous comma after id_count_view1) RBS On Sun, Nov 25, 2018 at 12:17 AM Igor Tandetnik wrote: > On 11/24/2018 6:59 PM, Bart Smissaert wrote: > > Have a table called Table1 like this: > > > > id Type > > -- > > 1 a > > 2 b > >

Re: [sqlite] Group by counting 2 different sub-groups

On 11/24/2018 6:59 PM, Bart Smissaert wrote: Have a table called Table1 like this: id Type -- 1 a 2 b 3 a 4 c 5 a 6 b 7 c 8 c 9 b 10 a Table create is this: CREATE TABLE [Table1]([id] INTEGER PRIMARY KEY, [Type] TEXT) Then there are 2 views, created like this CREATE VIEW View1 as

[sqlite] Group by counting 2 different sub-groups

Have a table called Table1 like this: id Type -- 1 a 2 b 3 a 4 c 5 a 6 b 7 c 8 c 9 b 10 a Table create is this: CREATE TABLE [Table1]([id] INTEGER PRIMARY KEY, [Type] TEXT) Then there are 2 views, created like this CREATE VIEW View1 as select id from Table1 where id < 8 CREATE VIEW

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

On 2018/06/30 3:12 PM, Luuk wrote: Ok ,my highway to hell start here (regargind the use of SQL) In SQLite3 you are allowed to do this: SELECT a,b,c FROM t1 GROUP BY a The values of 'b' and 'c' will be taken from a 'random' row... But if we rewrite this in SQL, i am getting something like

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

On 30-6-2018 15:45, Luuk wrote: > > In SQLite3 you are allowed to do this: > SELECT a,b,c > FROM t1 > GROUP BY a > > The values of 'b' and 'c' will be taken from a 'random' row... > > But if we rewrite this in SQL, i am getting something like this: > SELECT >a, >(SELECT MIN(b) FROM T1

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

If you want the row with the minimum B, and the row with a minimum C, then the union of two queries would seem to be appropriate. Gerry Snyder On Sat, Jun 30, 2018, 6:45 AM Luuk wrote: > > > On 30-6-2018 15:39, Abroży Nieprzełoży wrote: > >> SELECT > >>a, > >>(SELECT MIN(b) FROM T1

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

>>> SELECT >>>a, >>>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B, >>>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C >>> FROM t1 t >>> GROUP BY a >> Why not >> select a, min(b) as b, min(c) as c from t1 group by a; >> ? > > It still does not quarantee that the valuse show for b and c are

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

On 30-6-2018 15:39, Abroży Nieprzełoży wrote: >> SELECT >>a, >>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B, >>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C >> FROM t1 t >> GROUP BY a > Why not > select a, min(b) as b, min(c) as c from t1 group by a; > ? It still does not quarantee that

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

> SELECT >a, >(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B, >(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C > FROM t1 t > GROUP BY a Why not select a, min(b) as b, min(c) as c from t1 group by a; ? 2018-06-30 15:12 GMT+02:00, Luuk : > > On 30-6-2018 14:55, Keith Medcalf wrote: >> Note

[sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

On 30-6-2018 14:55, Keith Medcalf wrote: > Note that this is SQLite3 specific (and specific to Sybase of the era where > Microsoft SQL Server was actually just a rebranded Sybase, and Microsoft > re-writes of SQL Server up to about 2000). Technically you cannot do a query > of the form: > >

Re: [sqlite] Group by Literals

dcalf Sent: Wednesday, May 24, 2017 1:22 PM To: SQLite mailing list Subject: Re: [sqlite] Group by Literals This means that you can do things like: SELECT a, b, max(c) FROM t GROUP BY a; And you will be returned the groups of values of a, the max value of c in that group, and the value of b

Re: [sqlite] Group by Literals

On Wednesday, 24 May, 2017 06:07, Denis Burke wrote: > These all produce a single row of output (and it happens to be the last > row > inserted [a1,b5]): > select c1,c2 from t1 group by '1'; > select c1,c2 from t1 group by '2'; > select c1,c2 from t1 group by '3'; > select

Re: [sqlite] Group by Literals

Apologies for the multiple posts, but having now read the documentation thoroughly, I think the OP has a point and the GROUP BY documentation can benefit from local inclusion of the integer constant explanation that is given later for ORDER-BY (as quoted below) - or perhaps simply

Re: [sqlite] Group by Literals

list. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Denis Burke Gesendet: Mittwoch, 24. Mai 2017 14:07 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] Group by Literals The

Re: [sqlite] Group by Literals

Just realized, the section I've quoted from ORDER BY, not GROUP BY, but the effect pertains the both. On 2017/05/24 2:38 PM, R Smith wrote: This is quite clear in the documentation I think, and might even be made clear in the SQL standard (but I did not check). An integer literal (and only

Re: [sqlite] Group by Literals

Denis Burke wrote: > The SQLite documentation (http://www.sqlite.org/lang_select.html) says the > GROUP BY clause accepts [expr]. And [expr] can be composed of a literal. > What I cannot find is what SQLite does (or should do) with a literal in the > GROUP BY clause. SQL-92 doesn't allow it: |

Re: [sqlite] Group by Literals

This is quite clear in the documentation I think, and might even be made clear in the SQL standard (but I did not check). An integer literal (and only an integer literal) denotes the column number to order or group by. This is true for all Databases I know of, but that list is obviously not

[sqlite] Group by Literals

The SQLite documentation (http://www.sqlite.org/lang_select.html) says the GROUP BY clause accepts [expr]. And [expr] can be composed of a literal. What I cannot find is what SQLite does (or should do) with a literal in the GROUP BY clause. In the simple case of table T1 with two columns C1,C2

Re: [sqlite] Group contiguous rows (islands)

@ Pasma and Hainaut, Thanks again, that looks promising ! Jonathan Message: 42 Date: Wed, 15 Feb 2017 21:10:10 +0100 From: "E.Pasma" <pasm...@concepts.nl> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Group contiguous rows (islands)

Re: [sqlite] Group contiguous rows (islands)

Jean-Luc Hainaut: On 15/02/2017 18:34, E.Pasma wrote: Hello, the query below is simpler. May be slower. But looks pretty relational. Thanks, E Pasma. create table T(date integer,test char(12)); insert into T values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),

Re: [sqlite] Group contiguous rows (islands)

On 15/02/2017 18:34, E.Pasma wrote: Hello, the query below is simpler. May be slower. But looks pretty relational. Thanks, E Pasma. create table T(date integer,test char(12)); insert into T values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),

Re: [sqlite] Group contiguous rows (islands)

15 feb 2017, Jean-Luc Hainaut: You could try this, inspired by classic algorithms of temporal databases: create table T(date integer,test char(12)); insert into T values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'), (13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

Re: [sqlite] Group contiguous rows (islands)

@ Simon, Thanks for the input ! I was afraid someone was going to mention the dreaded recursive CTEs. Jonathan *** This e-mail message is intended only for the addressee(s) and contains information which may be

Re: [sqlite] Group contiguous rows (islands)

Jonathan -- Message: 79 Date: Wed, 15 Feb 2017 11:16:24 +0100 From: Clemens Ladisch <clem...@ladisch.de> To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Group contiguous rows (islands) Message-ID: <d689fbd5-0f4f-34ac-db98-72872d7a7...@ladisch.de> Content-Type: text/plain; ch

Re: [sqlite] Group contiguous rows (islands)

On 15 Feb 2017, at 11:58am, Petite Abeille wrote: > On Feb 15, 2017, at 11:16 AM, Clemens Ladisch wrote: > >> SQLite does not have windowing functions. > > A continuous/continual tragedy indeed :| Windowing breaks the philosophy behind SQL.

Re: [sqlite] Group contiguous rows (islands)

> On Feb 15, 2017, at 11:16 AM, Clemens Ladisch wrote: > > SQLite does not have windowing functions. A continuous/continual tragedy indeed :| Still, worthwhile mentioning The Tabibitosan Method, for reference purpose: http://www.orchestrapit.co.uk/?p=53

Re: [sqlite] Group contiguous rows (islands)

You could try this, inspired by classic algorithms of temporal databases: create table T(date integer,test char(12)); insert into T values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'), (13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb'); create table TT(seq integer not null

Re: [sqlite] Group contiguous rows (islands)

Rossel, Jonathan wrote: > Other database engines have solutions for this task (like windowing in > postgre) but I wonder if there is an efficient recipe in SQLite. SQLite does not have windowing functions. So the most efficient method would be to read the data with a simple ORDER BY, and do the

[sqlite] Group contiguous rows (islands)

Dear all, I need to perform a kind of partial GROUP BY to determine the beginnings and ends of sets of identical data. I can't use a full GROUP BY because these sets can be repeated and their repetition must be conserved. Other database engines have solutions for this task (like windowing in

[sqlite] GROUP BY with self join

On 2/14/2015 6:55 PM, R.Smith wrote: > > On 2/14/2015 6:32 PM, Bart Smissaert wrote: >> Having problems with the following SQL: >> >> 2 tables, trying to count occurrence of field 2 and field 3 of table 1 >> in field 1 (only field) of table 2. ... > I haven't tested it, but my quick-scan answer

[sqlite] GROUP BY with self join

On 2/14/2015 6:32 PM, Bart Smissaert wrote: > Having problems with the following SQL: > > 2 tables, trying to count occurrence of field 2 and field 3 of table 1 > in field 1 (only field) of table 2. > > Table 1 called ITEM with fields: NAME, DEFINITION1, DEFINITION2 > all text fields. Values in

[sqlite] GROUP BY with self join

Looked in the documentation and the answer seems to use total instead of sum. This is for the ratio field, not the 2 count fields. Looks all sorted now and thanks again. RBS On Sat, Feb 14, 2015 at 5:13 PM, Bart Smissaert wrote: > One more thing. How would I get the ratio of the 2 counts, so

[sqlite] GROUP BY with self join

One more thing. How would I get the ratio of the 2 counts, so count1 / (count1 + count2) ? select NAME, sum(INSTR(FULL_TEXT, DEFINITION1) > 0) as count1, sum(INSTR(FULL_TEXT, DEFINITION2) > 0) as count2, count1 / (count1 + count2) as ratio from Items join Descriptions group by NAME is

[sqlite] GROUP BY with self join

Thanks, that works nicely indeed. I tried the first SQL, will try second as well. I wasn't aware of the construction with join without the fields to join on after the join keyword. It looks strange to me. I take it the joining fields are done in the select. RBS On Sat, Feb 14, 2015 at 4:49 PM,

[sqlite] GROUP BY with self join

Having problems with the following SQL: 2 tables, trying to count occurrence of field 2 and field 3 of table 1 in field 1 (only field) of table 2. Table 1 called ITEM with fields: NAME, DEFINITION1, DEFINITION2 all text fields. Values in NAME are all unique. Table 2 called DESCRIPTIONS with

[sqlite] GROUP BY with self join

On 2/14/2015 11:32 AM, Bart Smissaert wrote: > SELECT I.ITEM_NAME, COUNT(D.ROWID), COUNT(D2.ROWID) FROM ITEMS I > INNER JOIN DESCRIPTIONS D ON (INSTR(D.FULL_TEXT, I.DEFINITION1) > 0) > INNER JOIN DESCRIPTIONS D2 ON (INSTR(D2.FULL_TEXT, I.DEFINITION2) > 0) > GROUP BY I.NAME You are doing a cross

Re: [sqlite] group question

On 10/18/2014 10:30 PM, ye yanhong wrote: select count(scode) as bb,scode from mytb where trues=1 and bb=3 group by scode; Error: misuse of aggregate: count() You are trying to use (indirectly, via bb alias) an aggregate function in a WHERE clause. You can't. WHERE clause specifies

[sqlite] group question

select count(scode) as bb,scode from mytb where trues=1 and bb=3 group by scode; Error: misuse of aggregate: count() ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP BY: ambiguous column name?

If you give your id columns unambiguous names to begin with, things will work out a lot better for you in the long run. May I suggest the following modification to your tables to remove ambiguity: CREATE TABLE x( x_id INTEGER ); CREATE TABLE y( y_id INTEGER, x_id INTEGER REFERENCES

Re: [sqlite] GROUP BY: ambiguous column name?

On Thu, 25 Sep 2014 20:32:29 +0200 Mark Lawrence wrote: > I would have expected the group to work the same as the order, given > that I think of the group as happening on the result set before any > joins. ORDER BY is different. It's not even *related* to GROUP BY. To

Re: [sqlite] GROUP BY: ambiguous column name?

This is just the way the SQL standard mandates it to work, not an oddity specific to SQLite. I imagine the optimizer is probably smart enough to not do the work twice here, but someone else will need to chime in to confirm that. If you want to be sure, you can use a CTE. On Thu, Sep 25, 2014 at

Re: [sqlite] GROUP BY: ambiguous column name?

On Thu Sep 25, 2014 at 01:43:20PM -0500, Cory Nelson wrote: > GROUP BY works on your input data, not output data. You want to GROUP > BY COALESCE(x.id, y.id) That describes the behaviour I demonstrated, but not the reasoning behind it nor the documentation pointing to that reasoning. Is SQLite

Re: [sqlite] GROUP BY: ambiguous column name?

GROUP BY works on your input data, not output data. You want to GROUP BY COALESCE(x.id, y.id) On Thu, Sep 25, 2014 at 1:37 PM, Mark Lawrence wrote: > On Thu Sep 25, 2014 at 08:32:29PM +0200, Mark Lawrence wrote: > > GROUP BY on a result column fails with "ambiguous column name":

Re: [sqlite] GROUP BY: ambiguous column name?

On Thu Sep 25, 2014 at 08:32:29PM +0200, Mark Lawrence wrote: > GROUP BY on a result column fails with "ambiguous column name": > > SELECT > COALESCE(x.id, y.id) AS id > FROM > y > LEFT JOIN > x > ON > x.id = y.fk > ORDER BY > id > ;

[sqlite] GROUP BY: ambiguous column name?

Don't know if this is a bug or intended behaviour. Given the following schema: CREATE TABLE x( id INTEGER ); CREATE TABLE y( id INTEGER, fk INTEGER REFERENCES x(id) ); ORDER BY on a result column name is allowed: SELECT COALESCE(x.id, y.id)

Re: [sqlite] GROUP BY months and years using integer data

>So that in all cases, "value" contains the value of stats.quantity when >the selected month shows up in the iteration loop, and Zero >when any other month shows up, so Summing the values (or indeed doing any >other kind of aggregate function on it) will only affect >stats.quantity values for the

Re: [sqlite] GROUP BY months and years using integer data

On 2013/12/22 20:53, James K. Lowden wrote: Similarly any attribute can be Boolean if it is found to be an attribute of an object. Giuseppe is not Boolean, but he is human and likely male, so that: (Giuseppe = Human) is true or 1, and (Giuseppe = Female) is false or 0. For RDBMS and indeed

Re: [sqlite] GROUP BY months and years using integer data

On 2013/12/22 09:55, Giuseppe Costanzi wrote: I don't know if I have understood well but the statment SUM(stats.quantity * (stats.which_month = 1)) SUM(stats.quantity * (stats.which_month = 2)) should be interpreted SUM stats.quantity IF stats.which_month = 1 is TRUE SUM stats.quantity IF

Re: [sqlite] GROUP BY months and years using integer data

On Sun, 22 Dec 2013 05:35:55 +0200 RSmith wrote: > Similarly any attribute can be Boolean if it is found to be an > attribute of an object. Giuseppe is not Boolean, but he is human and > likely male, so that: (Giuseppe = Human) is true or 1, and > (Giuseppe = Female) is false

Re: [sqlite] GROUP BY months and years using integer data

On 22-12-2013 08:55, Giuseppe Costanzi wrote: I don't know if I have understood well but the statment SUM(stats.quantity * (stats.which_month = 1)) SUM(stats.quantity * (stats.which_month = 2)) should be interpreted SUM stats.quantity IF stats.which_month = 1 is TRUE The original

Re: [sqlite] GROUP BY months and years using integer data

I don't know if I have understood well but the statment SUM(stats.quantity * (stats.which_month = 1)) SUM(stats.quantity * (stats.which_month = 2)) should be interpreted SUM stats.quantity IF stats.which_month = 1 is TRUE SUM stats.quantity IF stats.which_month = 2 is TRUE Am'I still in the

Re: [sqlite] GROUP BY months and years using integer data

Boolean Logic 101 - Feel free to skip if this is not your thread! - In addition to other replies - Boolean logic is interesting in that it has no real arithmetic value and can have only true or false as a value.

Re: [sqlite] GROUP BY months and years using integer data

On 21 Dec 2013, at 8:24pm, Giuseppe Costanzi wrote: > On Sat, Dec 21, 2013 at 9:11 PM, Simon Slavin wrote: > >> On 21 Dec 2013, at 7:45pm, Giuseppe Costanzi >> wrote: >> >>> I am not sure to have understood the

Re: [sqlite] GROUP BY months and years using integer data

...but stats.which_month is not a boolean, it represent a month... On Sat, Dec 21, 2013 at 9:11 PM, Simon Slavin wrote: > > On 21 Dec 2013, at 7:45pm, Giuseppe Costanzi > wrote: > > > I am not sure to have understood the Igor's solution > >

Re: [sqlite] GROUP BY months and years using integer data

On 21 Dec 2013, at 7:45pm, Giuseppe Costanzi wrote: > I am not sure to have understood the Igor's solution > > ...SUM(stats.quantity * (stats.which_month = 1)) AS gen > > which is the meaning of the operator * in the statement? Multiply. It works because

Re: [sqlite] GROUP BY months and years using integer data

thanks to all for the solutions. I am not sure to have understood the Igor's solution ...SUM(stats.quantity * (stats.which_month = 1)) AS gen which is the meaning of the operator * in the statement? regards and still thanks beppe On Sat, Dec 21, 2013 at 8:21 PM, James K. Lowden

Re: [sqlite] GROUP BY months and years using integer data

On Sat, 21 Dec 2013 17:30:50 +0200 RSmith wrote: > Just move the scope of the grouping and use Nulls in stead of 0's, > like this: > > SELECT stats.which_year AS year, > SUM(CASE WHEN stats.which_month = 1 THEN stats.quantity ELSE NULL > END) AS gen, ELSE NULL is the

Re: [sqlite] GROUP BY months and years using integer data

On 12/21/2013 10:30 AM, RSmith wrote: Just move the scope of the grouping and use Nulls in stead of 0's, like this: SELECT stats.which_year AS year, SUM(CASE WHEN stats.which_month = 1 THEN stats.quantity ELSE NULL END) AS gen, SUM(CASE WHEN stats.which_month = 2 THEN stats.quantity ELSE

Re: [sqlite] GROUP BY months and years using integer data

You are basically trying to group values where the individual values are different but each in itself accumulated in stead of accumulated for the grouping. Just move the scope of the grouping and use Nulls in stead of 0's, like this: SELECT stats.which_year AS year, SUM(CASE WHEN

[sqlite] GROUP BY months and years using integer data

HI all, I've a table such CREATE TABLE 'stats' ( 'stat_id' INTEGER PRIMARY KEY, 'test_id' INTEGER, 'quantity' INTEGER, 'which_month' INTEGER, 'which_year' INTEGER, ) and I need to extract data with somenthing like SELECT stats.which_year AS year, CASE WHEN

Re: [sqlite] GROUP BY

Yeah, something like SELECT order_id,issued,reference, company, department_id, SUM(quantity) AS quantity, SUM(dispatch) AS dispatch, SUM(surplus) AS surplus FROM orders_surplus GROUP BY order_id I have however to sum quantity, dispatch and surplus fields to force the view to calculate the

Re: [sqlite] GROUP BY

How to query and/or group complex SQL? Add the language to create a SQL VIEW before your SELECT statement: CREATE VIEW viewname AS SELECT [your SQL] http://www.sqlite.org/lang_createview.html Then use the viewname in a second SELECT statement as you would a table. SELECT order_id, issued,

Re: [sqlite] GROUP BY

HI clemens I've resolve with SELECT order_id,issued,reference, company, dep_id, SUM(qty) AS quantity, SUM(dispatch) AS dispatch, SUM(surplus) AS surplus FROM( SELECT orders.order_id AS order_id, orders.reference AS reference, strftime('%d-%m-%Y', orders.issued) AS issued,

Re: [sqlite] GROUP BY

Giuseppe Costanzi wrote: > order_idissuedcompany dep_id qty,dispatch surplus > "1""12-11-2013" "Siemens" "1" "6""4""2" > "1""12-11-2013" "Siemens" "1" "2""2""0" > "2""13-11-2013" "Siemens" "2" "10" "10"

[sqlite] GROUP BY

hi to everybody, you excuse for the preceding mails but I have had problems with this and I have had to change provider. However I propose my question. I have this query, that you also see in attachment file. SELECT orders.order_id AS order_id, strftime('%d-%m-%Y', orders.issued) AS issued,

Re: [sqlite] GROUP BY on SUM CASE

On 11/13/2013 9:51 AM, g.costa...@email.it wrote: I've this SELECT ... GROUP BY order_details.order_detail_id How I can group by order_id? "GROUP BY orders.order_id", I suppose. What exactly is the nature of the problem? -- Igor Tandetnik ___

Re: [sqlite] GROUP BY on SUM CASE

On 13 Nov 2013, at 2:51pm, g.costa...@email.it wrote: > I've this First, I can't read your post, it has too much HTML mixed up in it. Second, anything this complicated should probably be done in software, not by trying to use features of SQL. Simon.

[sqlite] GROUP BY on SUM CASE

hi, I've this SELECT orders.order_id AS order_id, strftime('%d-%m-%Y', orders.issued) AS issued, suppliers.company AS company, departments.department_id AS dep_id, order_details.quantity AS qty, SUM(CASE WHEN transactions.category_id = 1 THEN 1 ELSE 0 END) AS dispatch,

Re: [sqlite] Group by in sqlite 3.8 works a little differently depending on the spaces at the end

On Wed, Aug 28, 2013 at 5:11 PM, Igor Tandetnik wrote: > On 8/28/2013 8:57 AM, Max Vlasov wrote: > See the recent discussion at > > http://comments.gmane.org/gmane.comp.db.sqlite.general/83005 > > It's not about trailing spaces, but about whether Title in GROUP BY resolves >

Re: [sqlite] Group by in sqlite 3.8 works a little differently depending on the spaces at the end

On 8/28/2013 8:57 AM, Max Vlasov wrote: the following query (notice the space at the end of the 3rd string) Create table [TestTable] ([Title] TEXT); INsert into TestTable (Title) VALUES ('simple text'); INsert into TestTable (Title) VALUES ('simple text'); INsert into TestTable (Title) VALUES

  1   2   >