subject:"\[sqlite\] GROUP BY"


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 for this.

select gp_name,
  sum(emis_number in (select emis_number from diabetics),
  sum(emis_number in (select emis_number from on_non_insulin) OR
   emis_number in (select emis_number from on_insulin))
from patients group by gp_name;

--
Igor Tandetnik

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

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, 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 on_insulin are smaller sub_groups.
>
> RBS
>
> On Sun, Nov 25, 2018 at 1:41 AM Igor Tandetnik  wrote:
>
>> 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
>> diabetics)" is true for every row, guaranteed by the WHERE clause.
>> --
>> Igor Tandetnik
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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
on_insulin are smaller sub_groups.

RBS

On Sun, Nov 25, 2018 at 1:41 AM Igor Tandetnik  wrote:

> 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 diabetics)" is
> true for every row, guaranteed by the WHERE clause.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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 diabetics)" is true for every 
row, guaranteed by the WHERE clause.
--
Igor Tandetnik


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

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.  You could use the SQLite CLI tool
> to .dump the database as a text file, and paste it into a message.  If you
> do this, please thin your database down to just enough data to demonstrate
> your problem.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2018-11-24 Thread Simon Slavin

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 do 
this, please thin your database down to just enough data to demonstrate your 
problem.

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

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  wrote:

> 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 diabetics)
> > and emis_number in(select emis_number from on_non_insulin)
> > or emis_number in(select emis_number from on_insulin)
> > group by gp_name
> > order by pat_count asc
>
> Just in case you are not aware, AND has higher precedence than OR. Your
> query does ( (Diabetics AND Non-Insulin) OR Insulin ) as opposed to (
> Diabetics AND (Non-Insulin OR Insulin) ). Apologies if you knew that and
> really meant the former.
>
> > The above SQL works, but gives too low counts for diab_count .
>
> Show sample data, the result you expect, and the result you observe
> instead.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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 diabetics)
and emis_number in(select emis_number from on_non_insulin)
or emis_number in(select emis_number from on_insulin)
group by gp_name
order by pat_count asc


Just in case you are not aware, AND has higher precedence than OR. Your query 
does ( (Diabetics AND Non-Insulin) OR Insulin ) as opposed to ( Diabetics AND 
(Non-Insulin OR Insulin) ). Apologies if you knew that and really meant the 
former.


The above SQL works, but gives too low counts for diab_count .


Show sample data, the result you expect, and the result you observe instead.
--
Igor Tandetnik


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

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 emis_number from on_non_insulin)
or emis_number in(select emis_number from on_insulin)
group by gp_name
order by pat_count asc

patients is  a table, equivalent to Table1 in the simplified example and
diabetics, on_non_insulin and on_insulin
are views, holding subgroups of the id column in the table patients.
Emis_number is equivalent to id in the simplified example
and gp_name is equivalent to type in the simplified example.

The above SQL works, but gives too low counts for diab_count .

Any suggestion how to adapt?

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
> > 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 View2 as select id from Table1 where id < 5
> >
> > I can run a SQL like this:
> >
> > select type, count(id) as id_count_view2 from Table1
> > where id in(select id from view2)
> > group by type
> >
> > and that will give me:
> >
> > Type id_count_view2
> > 
> > a 2
> > b 1
> > c 1
> >
> > But I would like in 2 columns the counts of both views, so I would like
> > this output:
> >
> > Type id_count_view2 id_count_view1
> > -
> > a 2 3
> > b 1 2
> > c 1 2
> >
> > How should this be done?
>
>
> select type,
>sum(id in (select id from view2)) id_count_view2,
>sum(id in (select id from view1)) id_count_view1,
> from Table1
> group by type;
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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
> > 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 View2 as select id from Table1 where id < 5
> >
> > I can run a SQL like this:
> >
> > select type, count(id) as id_count_view2 from Table1
> > where id in(select id from view2)
> > group by type
> >
> > and that will give me:
> >
> > Type id_count_view2
> > 
> > a 2
> > b 1
> > c 1
> >
> > But I would like in 2 columns the counts of both views, so I would like
> > this output:
> >
> > Type id_count_view2 id_count_view1
> > -
> > a 2 3
> > b 1 2
> > c 1 2
> >
> > How should this be done?
>
>
> select type,
>sum(id in (select id from view2)) id_count_view2,
>sum(id in (select id from view1)) id_count_view1,
> from Table1
> group by type;
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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 select id from Table1 where id < 8
CREATE VIEW View2 as select id from Table1 where id < 5

I can run a SQL like this:

select type, count(id) as id_count_view2 from Table1
where id in(select id from view2)
group by type

and that will give me:

Type id_count_view2

a 2
b 1
c 1

But I would like in 2 columns the counts of both views, so I would like
this output:

Type id_count_view2 id_count_view1
-
a 2 3
b 1 2
c 1 2

How should this be done?



select type,
  sum(id in (select id from view2)) id_count_view2,
  sum(id in (select id from view1)) id_count_view1,
from Table1
group by type;

--
Igor Tandetnik

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

[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 View2 as select id from Table1 where id < 5

I can run a SQL like this:

select type, count(id) as id_count_view2 from Table1
where id in(select id from view2)
group by type

and that will give me:

Type id_count_view2

a 2
b 1
c 1

But I would like in 2 columns the counts of both views, so I would like
this output:

Type id_count_view2 id_count_view1
-
a 2 3
b 1 2
c 1 2

How should this be done?

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

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

2018-06-30 Thread R Smith


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 this:
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

QUESTION: How does one get the proper relationship between 'B' and 'C'?,


This question comes up from time to time, and is sometimes misunderstood 
by SQL users everywhere (or from every Engine I should say).


There is no easy solution and it isn't really an aggregate Set-Algebra 
problem, so mostly not handled specifically by Engines for aggregate 
queries.


To just state the problem more fully:

Consider this Table "Fruit" in a Grocer's shop:
SELECT * FROM Fruit;
  -- |  | |  DaysOn-
  -- Name    |   BasketNo   | Qty |   Shelf
  -- --- |  | --- | -
  -- Apples  |   1  |  40 | 12
  -- Apples  |   2  |  40 | 14
  -- Apples  |   3  |  23 | 16
  -- Oranges |   4  |  40 | 12
  -- Oranges |   5  |  40 | 14
  -- Oranges |   6  |  11 | 16
  -- Apples  |   7  |  7  | 22

An unopened basket contains 40 fruit. We try to use the oldest first, etc.
Now suppose we want to know what is the oldest unopened basket for every 
kind of fruit. The simplest query in *SQLite* would be:


SELECT Name, MAX(DaysOnShelf) AS MaxAge, BasketNo
  FROM Fruit
 WHERE Qty = 40
 GROUP BY Name
;

  -- Name    | MaxAge |   BasketNo
  -- --- | -- | 
  -- Apples  |   14   |   2
  -- Oranges |   14   |   5

Note that this ONLY works due to a peculiarity in SQLite, and it is 
forewarned in the SQLite documents that the non-aggregate field could 
return ANY random value that qualifies to be in a selected row (i.e. any 
row that is chosen for aggregation by the WHERE clause filter).


To say exactly what that means: In the query, for the Apples aggregate, 
two rows "pass" the filter where Qty = 40, namely those with baskets 1 
and 2. MAX(DaysOnShelf) for those two rows correctly returns 14, but 
there is no guarantee that the returned BasketNo will be from THAT 
specific row, it could have returned (still mathematically correct) 
Basket 1 in stead of Basket 2.  And, in a next release, it might even do 
so. You cannot assume what it would be.


In fact, if we change the query to enclose BasketNo too in an aggregate 
function (MIN() in this case):

SELECT Name, MAX(DaysOnShelf) AS MaxAge, MIN(BasketNo) AS BasketNo
  FROM Fruit
 WHERE Qty = 40
 GROUP BY Name
;

  -- Name    | MaxAge |   BasketNo
  -- --- | -- | 
  -- Apples  |   14   |   1
  -- Oranges |   14   |   4

Now the 14 and 1 next to Apples are definitely NOT from the same row - 
which is the entire point of aggregate functions, we want to know the 
truth over the whole set for each function, it is not intended to pick 
out values.


Thing is, most developers are precisely interested in the specific row 
containing the qualifying aggregate.


There are two ways to solve this (using standard SQL in any SQL engine - 
there might be more ways in specific Engines, such as SQLite).


1 - First find the target aggregate, then look that up against the 
original (non aggregate) set of records.

2 - Construct a custom taxonomy and deconstruct after.

1 is by far the most used and the easiest to write. It's downside is 
that it requires a double look-up loop, which in most Engines is fairly 
efficient anyway.

An example of this:

SELECT X.Name, X.MaxAge, F.BasketNo
  FROM (
    SELECT Name, MAX(DaysOnShelf) AS MaxAge, MAX(Qty) AS Qty
      FROM Fruit
     WHERE Qty = 40
     GROUP BY Name
  ) AS X
  JOIN Fruit AS F ON F.Name = X.Name AND F.DaysOnShelf = X.MaxAge AND 
F.Qty = X.Qty

;
  -- Name    | MaxAge |   BasketNo
  -- --- | -- | 
  -- Apples  |   14   |   2
  -- Oranges |   14   |   5


-- The above example shows ALL rows that match (as is the nature of a 
JOIN).  To only show a single row, the following can work:


SELECT X.Name, X.MaxAge, (
   SELECT F.BasketNo FROM Fruit AS F WHERE F.Name = X.Name AND 
F.DaysOnShelf = X.MaxAge AND F.Qty = X.Qty LIMIT 1

   ) AS BasketNo
  FROM (
    SELECT Name, MAX(DaysOnShelf) AS MaxAge, MAX(Qty) AS Qty
      FROM Fruit
     WHERE Qty = 40
     GROUP BY Name
  ) AS X
;
  -- Name    | MaxAge |   BasketNo
  -- --- | -- | 
  -- Apples  |   14   |   2
  -- Oranges |   14   |   5


-- And lastly, an example of achieving the first query with a CTE:

WITH X(Name, MaxAge, Qty) AS (
    SELECT Name, MAX(DaysOnShelf), MAX(Qty)
      FROM Fruit
     WHERE Qty = 40
     GROUP BY Name
)
SELECT X.Name, X.MaxAge, F.BasketNo
  FROM X
  JOIN Fruit AS F ON F.Name = X.Name AND F.DaysOnShelf = X.MaxAge AND

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

2018-06-30 Thread Luuk



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 WHERE a=t.a) AS B,
>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> FROM t1 t
> GROUP BY a
>
> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
> i mean how can one be use that both values are from the same row?
> This is not a problem to SQLite, because in SQLite the values of b and c
> seems to be originating from the same row, but what about *SQL* (if that
> exists...?)
>

I think i'll do this:

select x.a, t1.b, t1.c
from (select t1.a, min(t1.rowid)
    from t1
    group by t1.a) x
inner join t1 on x.rowid=t1.rowid;

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

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

2018-06-30 Thread Gerry Snyder

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 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 comming
> from the same row...
>
>
> >
> > 2018-06-30 15:12 GMT+02:00, Luuk :
> >> 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:
> >>>
> >>> SELECT c1, c2
> >>>   FROM t1
> >>> GROUP BY c2;
> >>>
> >>> because each column in the select list must be either an aggregate or
> >>> listed in the GROUP BY clause.  SQLite3 allows c1 to be a bare column
> >>> however and the value returned is taken from "some random row" of the
> >>> group.  If there are multiple such columns, they all come from the same
> >>> row in the group.  Although documented as a "random" row of the group,
> it
> >>> is the first (or last) row visited in the group while solving the query
> >>> (and this is of course subject to change but within the same version of
> >>> SQLite3 will deterministically be the row either first or last in the
> >>> visitation order -- the actual row may of course change depending on
> use
> >>> of indexes, etc).  You can re-write this part so it will work in other
> SQL
> >>> dialects that strictly enforce the requirement for c1 to be either an
> >>> aggregate or listed in the group by clause.
> >>>
> >>> ---
> >>> The fact that there's a Highway to Hell but only a Stairway to Heaven
> says
> >>> a lot about anticipated traffic volume.
> >>>
> >> 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 this:
> >> 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
> >>
> >> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
> >> i mean how can one be use that both values are from the same row?
> >> This is not a problem to SQLite, because in SQLite the values of b and c
> >> seems to be originating from the same row, but what about *SQL* (if that
> >> exists...?)
> >>
> >> --
> >> some test results:
> >> sqlite> insert into t1 values (1,1,2);
> >> sqlite> insert into t1 values (1,2,1);
> >> sqlite> insert into t1 values (2,2,1);
> >> sqlite> insert into t1 values (2,1,2);
> >> sqlite> select a,b,c from t1 group by a;
> >> 1|2|1
> >> 2|1|2
> >> sqlite> 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;
> >> 1|1|1
> >> 2|1|1
> >> sqlite>
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2018-06-30 Thread Abroży Nieprzełoży

>>> 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 comming
> from the same row...

select a, b, min(c) as c from (select a, min(b) as b from t1 group by
a) join t1 using(a, b) group by a, b;

?

2018-06-30 15:45 GMT+02:00, Luuk :
>
>
> 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 the valuse show for b and c are comming
> from the same row...
>
>
>>
>> 2018-06-30 15:12 GMT+02:00, Luuk :
>>> 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:

 SELECT c1, c2
   FROM t1
 GROUP BY c2;

 because each column in the select list must be either an aggregate or
 listed in the GROUP BY clause.  SQLite3 allows c1 to be a bare column
 however and the value returned is taken from "some random row" of the
 group.  If there are multiple such columns, they all come from the same
 row in the group.  Although documented as a "random" row of the group,
 it
 is the first (or last) row visited in the group while solving the query
 (and this is of course subject to change but within the same version of
 SQLite3 will deterministically be the row either first or last in the
 visitation order -- the actual row may of course change depending on use
 of indexes, etc).  You can re-write this part so it will work in other
 SQL
 dialects that strictly enforce the requirement for c1 to be either an
 aggregate or listed in the group by clause.

 ---
 The fact that there's a Highway to Hell but only a Stairway to Heaven
 says
 a lot about anticipated traffic volume.

>>> 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 this:
>>> 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
>>>
>>> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
>>> i mean how can one be use that both values are from the same row?
>>> This is not a problem to SQLite, because in SQLite the values of b and c
>>> seems to be originating from the same row, but what about *SQL* (if that
>>> exists...?)
>>>
>>> --
>>> some test results:
>>> sqlite> insert into t1 values (1,1,2);
>>> sqlite> insert into t1 values (1,2,1);
>>> sqlite> insert into t1 values (2,2,1);
>>> sqlite> insert into t1 values (2,1,2);
>>> sqlite> select a,b,c from t1 group by a;
>>> 1|2|1
>>> 2|1|2
>>> sqlite> 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;
>>> 1|1|1
>>> 2|1|1
>>> sqlite>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2018-06-30 Thread Luuk



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 the valuse show for b and c are comming
from the same row...


>
> 2018-06-30 15:12 GMT+02:00, Luuk :
>> 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:
>>>
>>> SELECT c1, c2
>>>   FROM t1
>>> GROUP BY c2;
>>>
>>> because each column in the select list must be either an aggregate or
>>> listed in the GROUP BY clause.  SQLite3 allows c1 to be a bare column
>>> however and the value returned is taken from "some random row" of the
>>> group.  If there are multiple such columns, they all come from the same
>>> row in the group.  Although documented as a "random" row of the group, it
>>> is the first (or last) row visited in the group while solving the query
>>> (and this is of course subject to change but within the same version of
>>> SQLite3 will deterministically be the row either first or last in the
>>> visitation order -- the actual row may of course change depending on use
>>> of indexes, etc).  You can re-write this part so it will work in other SQL
>>> dialects that strictly enforce the requirement for c1 to be either an
>>> aggregate or listed in the group by clause.
>>>
>>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to Heaven says
>>> a lot about anticipated traffic volume.
>>>
>> 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 this:
>> 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
>>
>> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
>> i mean how can one be use that both values are from the same row?
>> This is not a problem to SQLite, because in SQLite the values of b and c
>> seems to be originating from the same row, but what about *SQL* (if that
>> exists...?)
>>
>> --
>> some test results:
>> sqlite> insert into t1 values (1,1,2);
>> sqlite> insert into t1 values (1,2,1);
>> sqlite> insert into t1 values (2,2,1);
>> sqlite> insert into t1 values (2,1,2);
>> sqlite> select a,b,c from t1 group by a;
>> 1|2|1
>> 2|1|2
>> sqlite> 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;
>> 1|1|1
>> 2|1|1
>> sqlite>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

2018-06-30 Thread Abroży Nieprzełoży

> 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 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:
>>
>> SELECT c1, c2
>>   FROM t1
>> GROUP BY c2;
>>
>> because each column in the select list must be either an aggregate or
>> listed in the GROUP BY clause.  SQLite3 allows c1 to be a bare column
>> however and the value returned is taken from "some random row" of the
>> group.  If there are multiple such columns, they all come from the same
>> row in the group.  Although documented as a "random" row of the group, it
>> is the first (or last) row visited in the group while solving the query
>> (and this is of course subject to change but within the same version of
>> SQLite3 will deterministically be the row either first or last in the
>> visitation order -- the actual row may of course change depending on use
>> of indexes, etc).  You can re-write this part so it will work in other SQL
>> dialects that strictly enforce the requirement for c1 to be either an
>> aggregate or listed in the group by clause.
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to Heaven says
>> a lot about anticipated traffic volume.
>>
> 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 this:
> 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
>
> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
> i mean how can one be use that both values are from the same row?
> This is not a problem to SQLite, because in SQLite the values of b and c
> seems to be originating from the same row, but what about *SQL* (if that
> exists...?)
>
> --
> some test results:
> sqlite> insert into t1 values (1,1,2);
> sqlite> insert into t1 values (1,2,1);
> sqlite> insert into t1 values (2,2,1);
> sqlite> insert into t1 values (2,1,2);
> sqlite> select a,b,c from t1 group by a;
> 1|2|1
> 2|1|2
> sqlite> 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;
> 1|1|1
> 2|1|1
> sqlite>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2018-06-30 Thread Luuk


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:
>
> SELECT c1, c2
>   FROM t1
> GROUP BY c2;
>
> because each column in the select list must be either an aggregate or listed 
> in the GROUP BY clause.  SQLite3 allows c1 to be a bare column however and 
> the value returned is taken from "some random row" of the group.  If there 
> are multiple such columns, they all come from the same row in the group.  
> Although documented as a "random" row of the group, it is the first (or last) 
> row visited in the group while solving the query (and this is of course 
> subject to change but within the same version of SQLite3 will 
> deterministically be the row either first or last in the visitation order -- 
> the actual row may of course change depending on use of indexes, etc).  You 
> can re-write this part so it will work in other SQL dialects that strictly 
> enforce the requirement for c1 to be either an aggregate or listed in the 
> group by clause.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
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 this:
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

QUESTION: How does one get the proper relationship between 'B' and 'C'?,
i mean how can one be use that both values are from the same row?
This is not a problem to SQLite, because in SQLite the values of b and c
seems to be originating from the same row, but what about *SQL* (if that
exists...?)

-- 
some test results:
sqlite> insert into t1 values (1,1,2);
sqlite> insert into t1 values (1,2,1);
sqlite> insert into t1 values (2,2,1);
sqlite> insert into t1 values (2,1,2);
sqlite> select a,b,c from t1 group by a;
1|2|1
2|1|2
sqlite> 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;
1|1|1
2|1|1
sqlite>

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

Re: [sqlite] Group by Literals

2017-05-24 Thread David Raymond

A lot of what we're mentioning is in http://www.sqlite.org/lang_select.html in 
section 3.

For what Keith mentioned below a reminder that min() and max() are special 
cases where the bare columns are guaranteed to be from same row as (one of the) 
min or max values. Any other expressions are only guaranteed to be from "an 
undefined one of" the grouped rows. (But every column in any one single output 
row will have been calculated from the same input row)



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf
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 from (one of the rows) containing that maximum value. 
 The actual row (assuming multiple rows have the same max(c)) is undefined (but 
is in fact determined by the visitation order of the underlying table 
containing c).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Group by Literals

2017-05-24 Thread Keith Medcalf

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 c1,c2 from t1 group by 'c1';

> In the case of string literals, I would have expected to see all rows
> returned and in these cases the group by become effectively a no-op.  But
> I cannot find documentation to justify my expectation, or to validate the
> actual output.

In many SQL products this would yield an error of the stating that you can only 
select either (1) results appearing in the group by clause or (2) the result of 
an aggregate function applied to the group.

That is to say, you can ONLY use group by's of the following form:

SELECT ,  FROM t GROUP BY 

In the case of grouping by a constant, you have created a group that contains 
all rows.  SQLite, rather than tossing an error, permits you to request columns 
that do not conform with (1) and (2) above.  If you do so, then the returned 
values for those columns come from "one of the rows" comprising the group.  
Which particular row, is not defined (to average mortals that is -- in actual 
fact the values are taken from the last row "visited" that yielded the group 
aggregate result -- this is called "not defined" because it depends on the 
vagaries of query optimization and the available indexes -- for a given set of 
data and known indexes it is 100% determinate -- but that means that you are 
relying on an implementation detail.)  In your case it just happens to be the 
last row inserted because (1) a table scan is used to retrieve the data for the 
underlying table (ie, in order by rowid) and (b) the last row inserted happens 
to have the highest rowid.

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 from (one of the rows) containing that maximum value. 
 The actual row (assuming multiple rows have the same max(c)) is undefined (but 
is in fact determined by the visitation order of the underlying table 
containing c).

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

Re: [sqlite] Group by Literals

2017-05-24 Thread R Smith

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 
referring/linking to the ORDER-BY section for clarity.


It seems to be one of those things "we all just know" - but in truth, it 
isn't obvious from the current documentation.


Thanks,
Ryan


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 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 total.


A string literal simply groups by the value in that string, like any 
other expression that evaluates to a constant value , which can only 
ever return one single result since that value will be the same for 
all queried rows in the result-set.


To quote directly (via copy-paste) from the documentation you 
referenced, about 3/4 of the way down (emphasis by me):


...//
Each ORDER BY expression is processed as follows:

1.

   If the ORDER BY expression is a *constant integer K* then the
   expression is considered an alias for the K-th column of the result
   set (columns are numbered from left to right starting with 1).

2.

   If the ORDER BY expression is an identifier that corresponds to the
   alias of one of the output columns, then the expression is
   considered an alias for that column.

3.

   Otherwise, if the ORDER BY expression is *any other expression*, it
   is evaluated and the returned value used to order the output rows//...


As far as clarity goes I think the devs did an amicable job here - 
it's hard for me to come up with a similarly succinct statement that 
would make point 1 any clearer.


Hope this answers the question, Good luck!
Ryan



On 2017/05/24 2:07 PM, 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.

In the simple case of table T1 with two columns C1,C2

CREATE TABLE [T1](
 [c1] text COLLATE NOCASE,
 [c2] text COLLATE NOCASE);

INSERT INTO T1 VALUES ('a0','b8'),('a1','b7'),('a2','b8'),('a1','b5');

These queries give the same results:
SELECT c1,c2 from T1 GROUP BY c1;
SELECT c1,c2 from T1 GROUP BY 1;
SELECT c2,c1 from T1 GROUP BY 2;

and these two give the same result:
SELECT c1,c2 from T1 GROUP BY c2;
SELECT c1,c2 from T1 GROUP BY 2;

and this produces an error:
SELECT c1,c2 from T1 GROUP BY 3;

This seems to show in the case of an integer literal, it substitutes the
integer for that number of the selected columns.  Maybe this is 
obvious and
documented somewhere, but I was not able to find it and was not 
obvious to

me initially.

But when the literal GROUP BY is a string:

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 c1,c2 from t1 group by 'c1';

In the case of string literals, I would have expected to see all rows
returned and in these cases the group by become effectively a no-op.  
But I

cannot find documentation to justify my expectation, or to validate the
actual output.

I did compare to SQL Server to see if behavior was similar and SQL 
Server

simply does not allow this and complains "Each GROUP BY expression must
contain at least one column that is not an outer reference."

So I think my questions are simply:
1) should literals be allowed in the group by?
2) if so, is SQLite doing what it should with them?

Thanks,
Denis
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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


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

Re: [sqlite] Group by Literals

2017-05-24 Thread Hick Gunter

The number 1 references the first column of the result set, 2 the second, and 3 
is an error because there are only 2 columns.

If the expression is a constant, then there is only 1 group.

You are missing any meaningful information because you do not have an aggregate 
expression in your select 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 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

CREATE TABLE [T1](
[c1] text COLLATE NOCASE,
[c2] text COLLATE NOCASE);

INSERT INTO T1 VALUES ('a0','b8'),('a1','b7'),('a2','b8'),('a1','b5');

These queries give the same results:
SELECT c1,c2 from T1 GROUP BY c1;
SELECT c1,c2 from T1 GROUP BY 1;
SELECT c2,c1 from T1 GROUP BY 2;

and these two give the same result:
SELECT c1,c2 from T1 GROUP BY c2;
SELECT c1,c2 from T1 GROUP BY 2;

and this produces an error:
SELECT c1,c2 from T1 GROUP BY 3;

This seems to show in the case of an integer literal, it substitutes the 
integer for that number of the selected columns.  Maybe this is obvious and 
documented somewhere, but I was not able to find it and was not obvious to me 
initially.

But when the literal GROUP BY is a string:

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 c1,c2 from t1 group by 'c1';

In the case of string literals, I would have expected to see all rows returned 
and in these cases the group by become effectively a no-op.  But I cannot find 
documentation to justify my expectation, or to validate the actual output.

I did compare to SQL Server to see if behavior was similar and SQL Server 
simply does not allow this and complains "Each GROUP BY expression must contain 
at least one column that is not an outer reference."

So I think my questions are simply:
1) should literals be allowed in the group by?
2) if so, is SQLite doing what it should with them?

Thanks,
Denis
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Group by Literals

2017-05-24 Thread R Smith

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 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 total.


A string literal simply groups by the value in that string, like any 
other expression that evaluates to a constant value , which can only 
ever return one single result since that value will be the same for 
all queried rows in the result-set.


To quote directly (via copy-paste) from the documentation you 
referenced, about 3/4 of the way down (emphasis by me):


...//
Each ORDER BY expression is processed as follows:

1.

   If the ORDER BY expression is a *constant integer K* then the
   expression is considered an alias for the K-th column of the result
   set (columns are numbered from left to right starting with 1).

2.

   If the ORDER BY expression is an identifier that corresponds to the
   alias of one of the output columns, then the expression is
   considered an alias for that column.

3.

   Otherwise, if the ORDER BY expression is *any other expression*, it
   is evaluated and the returned value used to order the output rows//...


As far as clarity goes I think the devs did an amicable job here - 
it's hard for me to come up with a similarly succinct statement that 
would make point 1 any clearer.


Hope this answers the question, Good luck!
Ryan



On 2017/05/24 2:07 PM, 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.

In the simple case of table T1 with two columns C1,C2

CREATE TABLE [T1](
 [c1] text COLLATE NOCASE,
 [c2] text COLLATE NOCASE);

INSERT INTO T1 VALUES ('a0','b8'),('a1','b7'),('a2','b8'),('a1','b5');

These queries give the same results:
SELECT c1,c2 from T1 GROUP BY c1;
SELECT c1,c2 from T1 GROUP BY 1;
SELECT c2,c1 from T1 GROUP BY 2;

and these two give the same result:
SELECT c1,c2 from T1 GROUP BY c2;
SELECT c1,c2 from T1 GROUP BY 2;

and this produces an error:
SELECT c1,c2 from T1 GROUP BY 3;

This seems to show in the case of an integer literal, it substitutes the
integer for that number of the selected columns.  Maybe this is 
obvious and
documented somewhere, but I was not able to find it and was not 
obvious to

me initially.

But when the literal GROUP BY is a string:

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 c1,c2 from t1 group by 'c1';

In the case of string literals, I would have expected to see all rows
returned and in these cases the group by become effectively a no-op.  
But I

cannot find documentation to justify my expectation, or to validate the
actual output.

I did compare to SQL Server to see if behavior was similar and SQL 
Server

simply does not allow this and complains "Each GROUP BY expression must
contain at least one column that is not an outer reference."

So I think my questions are simply:
1) should literals be allowed in the group by?
2) if so, is SQLite doing what it should with them?

Thanks,
Denis
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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


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

Re: [sqlite] Group by Literals

2017-05-24 Thread Clemens Ladisch

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:
| Each  in the  shall unambiguously
| reference a column of T.

But ORDER BY supports referencing columns by their index:
| If a  contains an , then the
|  shall be greater than 0 and not greater than the
| degree of T. The  identifies the column of T
| with the ordinal position specified by the .

SQLite extends GROUP BY to also allow this kind of column references.

> In the case of string literals, I would have expected to see all rows
> returned and in these cases the group by become effectively a no-op.

| The result of the  is a partitioning of T into
| a set of groups. The set is the minimum number of groups such
| that, for each grouping column of each group of more than one
| row, no two values of that grouping column are distinct.

If we say that the "value of the grouping column" is the value of
the expression itself, then the query must result in a single group
because no two values are distinct in this column in any of the rows.

Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Group by Literals

2017-05-24 Thread R Smith

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 total.


A string literal simply groups by the value in that string, like any 
other expression that evaluates to a constant value , which can only 
ever return one single result since that value will be the same for all 
queried rows in the result-set.


To quote directly (via copy-paste) from the documentation you 
referenced, about 3/4 of the way down (emphasis by me):


...//
Each ORDER BY expression is processed as follows:

1.

   If the ORDER BY expression is a *constant integer K* then the
   expression is considered an alias for the K-th column of the result
   set (columns are numbered from left to right starting with 1).

2.

   If the ORDER BY expression is an identifier that corresponds to the
   alias of one of the output columns, then the expression is
   considered an alias for that column.

3.

   Otherwise, if the ORDER BY expression is *any other expression*, it
   is evaluated and the returned value used to order the output rows//...


As far as clarity goes I think the devs did an amicable job here - it's 
hard for me to come up with a similarly succinct statement that would 
make point 1 any clearer.


Hope this answers the question, Good luck!
Ryan



On 2017/05/24 2:07 PM, 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.

In the simple case of table T1 with two columns C1,C2

CREATE TABLE [T1](
 [c1] text COLLATE NOCASE,
 [c2] text COLLATE NOCASE);

INSERT INTO T1 VALUES ('a0','b8'),('a1','b7'),('a2','b8'),('a1','b5');

These queries give the same results:
SELECT c1,c2 from T1 GROUP BY c1;
SELECT c1,c2 from T1 GROUP BY 1;
SELECT c2,c1 from T1 GROUP BY 2;

and these two give the same result:
SELECT c1,c2 from T1 GROUP BY c2;
SELECT c1,c2 from T1 GROUP BY 2;

and this produces an error:
SELECT c1,c2 from T1 GROUP BY 3;

This seems to show in the case of an integer literal, it substitutes the
integer for that number of the selected columns.  Maybe this is obvious and
documented somewhere, but I was not able to find it and was not obvious to
me initially.

But when the literal GROUP BY is a string:

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 c1,c2 from t1 group by 'c1';

In the case of string literals, I would have expected to see all rows
returned and in these cases the group by become effectively a no-op.  But I
cannot find documentation to justify my expectation, or to validate the
actual output.

I did compare to SQL Server to see if behavior was similar and SQL Server
simply does not allow this and complains "Each GROUP BY expression must
contain at least one column that is not an outer reference."

So I think my questions are simply:
1) should literals be allowed in the group by?
2) if so, is SQLite doing what it should with them?

Thanks,
Denis
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] Group by Literals

2017-05-24 Thread Denis Burke

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

CREATE TABLE [T1](
[c1] text COLLATE NOCASE,
[c2] text COLLATE NOCASE);

INSERT INTO T1 VALUES ('a0','b8'),('a1','b7'),('a2','b8'),('a1','b5');

These queries give the same results:
SELECT c1,c2 from T1 GROUP BY c1;
SELECT c1,c2 from T1 GROUP BY 1;
SELECT c2,c1 from T1 GROUP BY 2;

and these two give the same result:
SELECT c1,c2 from T1 GROUP BY c2;
SELECT c1,c2 from T1 GROUP BY 2;

and this produces an error:
SELECT c1,c2 from T1 GROUP BY 3;

This seems to show in the case of an integer literal, it substitutes the
integer for that number of the selected columns.  Maybe this is obvious and
documented somewhere, but I was not able to find it and was not obvious to
me initially.

But when the literal GROUP BY is a string:

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 c1,c2 from t1 group by 'c1';

In the case of string literals, I would have expected to see all rows
returned and in these cases the group by become effectively a no-op.  But I
cannot find documentation to justify my expectation, or to validate the
actual output.

I did compare to SQL Server to see if behavior was similar and SQL Server
simply does not allow this and complains "Each GROUP BY expression must
contain at least one column that is not an outer reference."

So I think my questions are simply:
1) should literals be allowed in the group by?
2) if so, is SQLite doing what it should with them?

Thanks,
Denis
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Group contiguous rows (islands)

2017-02-16 Thread Rossel, Jonathan

@ 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)
Message-ID: <55fa9699-22fe-4dd9-9b86-36a190485...@concepts.nl>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes

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'),
>> (13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');
>>
>> select min(date) as fromdate, max(date) as enddate, test
>> from(--get closest preceeding different key
>>select t.*, max(t2.date) as key2
>>from t
>>left join t t2
>>on t2.datet.test
>>group by t.date
>>)
>> group by key2
>
> Quite nice solution indeed!
> For those who may feel uncomfortable with outer joins, the from  
> clause could be written as a subquery:
>
> from (select date, test, (select  max(date)
>  fromt t2
>  where  t2.date < t.date
>  and  t2.test <> t.test)  
> as key2)
>
> Thanks
>
> J-L
>
this way you may also try to optimise speed by using ORDER BY & LIMIT  
1 instead of MAX

from (select date, test, (select t2.date
   from  t t2
   where t2.date < t.date
   and t2.test <>  t.test
   order by t2.date desc limit 1)  
as key2

***
This e-mail message is intended only for the addressee(s) and contains
information which may be confidential. If you are not the intended
recipient please do not read, save, forward, disclose or copy the contents
of this e-mail. If this e-mail has been sent to you in error, please delete 
this 
e-mail and any copies or links to this e-mail completely and immediately
from your system. We also like to inform you that communication via e-mail
over the Internet is insecure because third parties may have the possibility
to access and manipulate e-mails.

Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of
The Swatch Group Ltd.
***
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread E.Pasma



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'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

select min(date) as fromdate, max(date) as enddate, test
from(--get closest preceeding different key
   select t.*, max(t2.date) as key2
   from t
   left join t t2
   on t2.datet.test
   group by t.date
   )
group by key2


Quite nice solution indeed!
For those who may feel uncomfortable with outer joins, the from  
clause could be written as a subquery:


from (select date, test, (select  max(date)
 fromt t2
 where  t2.date < t.date
 and  t2.test <> t.test)  
as key2)


Thanks

J-L

this way you may also try to optimise speed by using ORDER BY & LIMIT  
1 instead of MAX


from (select date, test, (select t2.date
  from  t t2
  where t2.date < t.date
  and t2.test <>  t.test
  order by t2.date desc limit 1)  
as key2

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

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread 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'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

select min(date) as fromdate, max(date) as enddate, test
from(--get closest preceeding different key
select t.*, max(t2.date) as key2
from t
left join t t2
on t2.datet.test
group by t.date
)
group by key2


Quite nice solution indeed!
For those who may feel uncomfortable with outer joins, the from clause 
could be written as a subquery:


from (select date, test, (select  max(date)
  fromt t2
  where  t2.date < t.date
  and  t2.test <> t.test) 
as key2)


Thanks

J-L

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

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread E.Pasma


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');

create table TT(seq integer not null primary key autoincrement,date  
integer,test char(12));

insert into TT(date,test) select * from T order by date;

select T1.date, T3.date, T1.test
from   TT T1, TT T3
-- More efficient than "where  T1.date <= T3.date"
where  T1.seq <= T3.seq
andT1.test = T3.test
andnot exists(select * from TT where seq = T1.seq-1 and test =  
T1.test)
andnot exists(select * from TT where seq = T3.seq+1 and test =  
T3.test)

andnot exists(select *
from   TT T2
-- More efficient than "where  T2.date between  
T1.date and T3.date"

where  T2.seq between T1.seq and T3.seq
andT2.test <> T1.test);

Result:

+--+--+--+
| date | date | test |
+--+--+--+
| 1| 3| clim |
| 7| 10   | amb  |
| 12   | 12   | xxx  |
| 13   | 20   | clim |
| 22   | 25   | amb  |
+--+--+--+

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'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

select min(date) as fromdate, max(date) as enddate, test
from(--get closest preceeding different key
select t.*, max(t2.date) as key2
from t
left join t t2
on t2.datet.test
group by t.date
)
group by key2
;


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

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Rossel, Jonathan

@ 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 confidential. If you are not the intended
recipient please do not read, save, forward, disclose or copy the contents
of this e-mail. If this e-mail has been sent to you in error, please delete 
this 
e-mail and any copies or links to this e-mail completely and immediately
from your system. We also like to inform you that communication via e-mail
over the Internet is insecure because third parties may have the possibility
to access and manipulate e-mails.

Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of
The Swatch Group Ltd.
***

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

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Rossel, Jonathan

@ Clemens, Petite Abeille,

Thanks, that's what I thought, but it's comforting to know for sure...

@ Jean-Luc,

Thanks a lot for the detailed answer, that's awesome ! I'll give it a try and 
see how it compares with an external "manual" grouping


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; charset=us-ascii

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 grouping
in your code.


Regards,
Clemens


--

Message: 83
Date: Wed, 15 Feb 2017 12:02:32 +0100
From: Jean-Luc Hainaut <jean-luc.hain...@unamur.be>
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Group contiguous rows (islands)
Message-ID: <58a43548@unamur.be>
Content-Type: text/plain; charset=UTF-8; format=flowed


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 primary key autoincrement,date 
integer,test char(12));
insert into TT(date,test) select * from T order by date;

select T1.date, T3.date, T1.test
from   TT T1, TT T3
-- More efficient than "where  T1.date <= T3.date"
where  T1.seq <= T3.seq
andT1.test = T3.test
andnot exists(select * from TT where seq = T1.seq-1 and test = T1.test)
andnot exists(select * from TT where seq = T3.seq+1 and test = T3.test)
andnot exists(select *
  from   TT T2
  -- More efficient than "where  T2.date between T1.date 
and T3.date"
  where  T2.seq between T1.seq and T3.seq
  andT2.test <> T1.test);

Result:

+--+--+--+
| date | date | test |
+--+--+--+
| 1| 3| clim |
| 7| 10   | amb  |
| 12   | 12   | xxx  |
| 13   | 20   | clim |
| 22   | 25   | amb  |
+--+--+--+

Working table TT is recommended to create an ordered sequence of rows in 
which "next" and "previous" rows are more easily described than in the 
source table. Avoid "order by" on views. It works in SQLite but it 
should not!

The idea is to identify maximal sequences of identical "test" values as 
follow:
- T1 denotes the first row of a sequence
- T3 the last row
- T2 any "disturbing" row lying between T1 and T3 but with a different 
value of "test"
- first "not exists" condition states that T1 must be the very first of 
the sequence: it must not be immediately preceded by a row with same 
value of "test"
- same for second "not exists" condition: T3 must be the last
- the third "not exists" condition states that there is no "disturbing" 
row between T1 and T3.

Valid if maximal sequences do not overlap. This query also detects 
single row sequences (e.g., 'xxx').
An index on TT.test may be useful to support T1*T3 join.

For large tables, an iterative procedure will be faster, though less 
elegant!

Regards

Jean-Luc Hainaut


--

Message: 89
Date: Wed, 15 Feb 2017 12:58:07 +0100
From: Petite Abeille <petite.abei...@gmail.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Group contiguous rows (islands)
Message-ID: <4b88b85b-75eb-4391-989e-198ebe31e...@gmail.com>
Content-Type: text/plain; charset=us-ascii


> On Feb 15, 2017, at 11:16 AM, Clemens Ladisch <clem...@ladisch.de> 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
https://community.oracle.com/message/3991678

Rather nifty in its simplicity and power. Sadly, out of reach to SQLite 
dwellers.






--

Subject: Digest Footer

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


--

End of sqlite-users Digest, Vol 110, Issue 15
*
***
This e-mail message is intended only for the addressee(s) and contains
information which may be

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Simon Slavin

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.  Rows are meant to be members of a 
set, and your operations on them are meant to be set operations.  There is no 
implicit order for set elements.  That’s why bare-bones SQL implementations 
don’t have cursors or windowing.

> Still, worthwhile mentioning The Tabibitosan Method, for reference purpose:
> 
> http://www.orchestrapit.co.uk/?p=53
> https://community.oracle.com/message/3991678
> 
> Rather nifty in its simplicity and power. Sadly, out of reach to SQLite 
> dwellers.

Actually SQLite can do it, by iterating using recursive common table 
expressions:

.  It looks like an interesting programming exercise, though there’s a danger 
it will lead to unreadable code (a perpetual danger with WITH).  You would need 
a spare column in the table to store the 'group' values in.

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

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Petite Abeille


> 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
https://community.oracle.com/message/3991678

Rather nifty in its simplicity and power. Sadly, out of reach to SQLite 
dwellers.




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

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread 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');

create table TT(seq integer not null primary key autoincrement,date 
integer,test char(12));

insert into TT(date,test) select * from T order by date;

select T1.date, T3.date, T1.test
from   TT T1, TT T3
-- More efficient than "where  T1.date <= T3.date"
where  T1.seq <= T3.seq
andT1.test = T3.test
andnot exists(select * from TT where seq = T1.seq-1 and test = T1.test)
andnot exists(select * from TT where seq = T3.seq+1 and test = T3.test)
andnot exists(select *
 from   TT T2
 -- More efficient than "where  T2.date between T1.date 
and T3.date"

 where  T2.seq between T1.seq and T3.seq
 andT2.test <> T1.test);

Result:

+--+--+--+
| date | date | test |
+--+--+--+
| 1| 3| clim |
| 7| 10   | amb  |
| 12   | 12   | xxx  |
| 13   | 20   | clim |
| 22   | 25   | amb  |
+--+--+--+

Working table TT is recommended to create an ordered sequence of rows in 
which "next" and "previous" rows are more easily described than in the 
source table. Avoid "order by" on views. It works in SQLite but it 
should not!


The idea is to identify maximal sequences of identical "test" values as 
follow:

- T1 denotes the first row of a sequence
- T3 the last row
- T2 any "disturbing" row lying between T1 and T3 but with a different 
value of "test"
- first "not exists" condition states that T1 must be the very first of 
the sequence: it must not be immediately preceded by a row with same 
value of "test"

- same for second "not exists" condition: T3 must be the last
- the third "not exists" condition states that there is no "disturbing" 
row between T1 and T3.


Valid if maximal sequences do not overlap. This query also detects 
single row sequences (e.g., 'xxx').

An index on TT.test may be useful to support T1*T3 join.

For large tables, an iterative procedure will be faster, though less 
elegant!


Regards

Jean-Luc Hainaut

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

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Clemens Ladisch

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 grouping
in your code.

Regards,
Clemens

> This e-mail message is intended only ...

This e-mail contains public information intended for any subscriber of
this mailing list and for anybody else who bothers to read it; it will
be copied, disclosed and distributed to the public.  If you think you
are not the intended recipient, please commit suicide immediately.
These terms apply also to any e-mails quoted in, referenced from, or
answering this e-mail, and supersede any confidentiality notices in
those e-mails.  Additionally, confidentiality notices in those e-mails
will incur legal processing fees of $42 per line; you have agreed to
this by reading this confidentiality notice.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Group contiguous rows (islands)

2017-02-15 Thread Rossel, Jonathan

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 postgre) but I wonder if there 
is an efficient recipe in SQLite.

Example:
===

Table: mytable


date test
-- --
1   clim
3   clim
7   amb
10  amb
13  clim
15  clim
20  clim
22  amb
25  amb

Desired result
-

date_fromdate_totest
---   --
1 3  clim
7 10amb
13   15 clim
22   25 amb


(non optimal) solution found
=

CREATE VIEW mytablebydate
AS  -- Pre-order table to avoid ordering it twice in sub-queries
SELECT * FROM mytable ORDER BY date

CREATE VIEW mytablenext
AS
SELECT  date,
test,
(
-- first row > this row
SELECT date   -- NULL if not exists
FROM mytablebydate
WHERE date > MT.date
LIMIT 1
) as date_next,
(
-- first row > this row
SELECT test   -- NULL if not exists
FROM mytablebydate
WHERE date > MT.date
LIMIT 1
) as test_next  
FROM mytable MT
WHERE test != test_next

-- Get desired results
SELECT  (
--  Date of the previous row
SELECT MAX( date_next )
FROM mytablenext
WHERE date_next < mt.date
) AS date_from,

date AS date_to,   -- this row
test
FROM mytablenext mt


Comments


This method returns a Null for the first date_from and the last group is not 
returned. It is therefore incomplete. In addition, it involves quite a lot of 
subqueries. For completeness, it is inspired by 
http://stackoverflow.com/questions/30455227/date-range-for-set-of-same-data/30460263#30460263.
 So, is there a better / official way in SQLite ?

Any help will be welcome,

Jonathan

***
This e-mail message is intended only for the addressee(s) and contains
information which may be confidential. If you are not the intended
recipient please do not read, save, forward, disclose or copy the contents
of this e-mail. If this e-mail has been sent to you in error, please delete 
this 
e-mail and any copies or links to this e-mail completely and immediately
from your system. We also like to inform you that communication via e-mail
over the Internet is insecure because third parties may have the possibility
to access and manipulate e-mails.

Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of
The Swatch Group Ltd.
***
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] GROUP BY with self join

2015-02-14 Thread R.Smith

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 would be//

Oops - After seeing Igor's reply, I realized those "COUNT" statements in 
my last examples should better be SUM statements since count will give 
row-hits not item totals.

[sqlite] GROUP BY with self join

2015-02-14 Thread R.Smith

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 NAME are all unique.
>
> Table 2 called DESCRIPTIONS with only one field, FULL_TEXT also a text
> column.
>
> This is the SQL that I think should work but doesn't as it takes very long
> and produces
> too high counts:
>
> 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

I haven't tested it, but my quick-scan answer would be that you cannot 
refer Descriptions twice like that. You are essentially asking the 
engine to match every entry in table DESCRIPTIONS (D2) to every entry in 
the same DESCRIPTIONS (D1) and then multiply it by matching to every 
entry in ITEMS and keep those where an INSTR functions succeeds - 
something that cannot be optimized out by the QP - i.e. which all will 
take very long.

If you really want the total number of times a definition for Name in 
either the first or second definition, then these SQL statements might work:

SELECT I.ITEM_NAME, COUNT() FROM ITEMS I
INNER JOIN DESCRIPTIONS D ON (INSTR(D.FULL_TEXT, I.DEFINITION1) > 0) OR 
(INSTR(D.FULL_TEXT, I.DEFINITION2) > 0)
GROUP BY I.NAME

If however you want the total amount of times separate for Definitions 1 and 2, 
then some other method is needed, like this possibility:

SELECT * FROM (
   SELECT I.ITEM_NAME, 1 AS Kind, COUNT() FROM ITEMS I
   INNER JOIN DESCRIPTIONS D ON (INSTR(D.FULL_TEXT, I.DEFINITION1) > 0)
   UNION ALL
   SELECT I.ITEM_NAME, 2 AS Kind, COUNT() FROM ITEMS I
   INNER JOIN DESCRIPTIONS D ON (INSTR(D.FULL_TEXT, I.DEFINITION2) > 0)
)
GROUP BY I.NAME, Kind

And finally, to achieve the exact thing you asked for (but I think not 
efficiently):

SELECT I.ITEM_NAME,
   COUNT(CASE INSTR(D.FULL_TEXT, I.DEFINITION1) WHEN 0 THEN 0 ELSE 1 END)) AS 
CountDef1
   COUNT(CASE INSTR(D.FULL_TEXT, I.DEFINITION2) WHEN 0 THEN 0 ELSE 1 END)) AS 
CountDef2
FROM ITEMS I
JOIN DESCRIPTIONS D
GROUP BY I.NAME

or you can use a boolean short-cut (because TRUE evaluates to 1) to achieve the 
exact same with:

SELECT I.ITEM_NAME,
   COUNT(INSTR(D.FULL_TEXT, I.DEFINITION1)>0) AS CountDef1
   COUNT(INSTR(D.FULL_TEXT, I.DEFINITION2)>0) AS CountDef2
FROM ITEMS I
JOIN DESCRIPTIONS D
GROUP BY I.NAME

If this doesn't work for you, or if any of this works for you and you 
are not sure howcome it specifically works, feel free to ask and I (or 
someone else here) will be glad to dissect it.

[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 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 not allowed and
>
> select NAME,
>   sum(INSTR(FULL_TEXT, DEFINITION1) > 0) as count1,
>   sum(INSTR(FULL_TEXT, DEFINITION2) > 0) as count2,
>   sum(INSTR(FULL_TEXT, DEFINITION1) > 0) /
>   (sum(INSTR(FULL_TEXT, DEFINITION1) > 0) +
>   sum(INSTR(FULL_TEXT, DEFINITION2) > 0)) as ratio
>  from Items join Descriptions
> group by NAME
>
> Gives integer values, so no decimals.
>
> RBS
>
>
>
> On Sat, Feb 14, 2015 at 4:49 PM, Igor Tandetnik 
> wrote:
>
>> 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 product of DESCRIPTIONS to itself. I expect the two
>> counts are the same, and are equal to a product of the actual, true counts.
>>
>> You are looking for something like this:
>>
>> select NAME,
>>   sum(INSTR(FULL_TEXT, DEFINITION1) > 0),
>>   sum(INSTR(FULL_TEXT, DEFINITION2) > 0),
>> from Items join Descriptions
>> group by NAME;
>>
>> -- or
>>
>> select NAME,
>>   (select count(*) from Descriptions where INSTR(FULL_TEXT, DEFINITION1)
>> > 0),
>>   (select count(*) from Descriptions where INSTR(FULL_TEXT, DEFINITION2)
>> > 0)
>> from Items;
>>
>> The first one would probably work faster.
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

[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 not allowed and

select NAME,
  sum(INSTR(FULL_TEXT, DEFINITION1) > 0) as count1,
  sum(INSTR(FULL_TEXT, DEFINITION2) > 0) as count2,
  sum(INSTR(FULL_TEXT, DEFINITION1) > 0) /
  (sum(INSTR(FULL_TEXT, DEFINITION1) > 0) +
  sum(INSTR(FULL_TEXT, DEFINITION2) > 0)) as ratio
 from Items join Descriptions
group by NAME

Gives integer values, so no decimals.

RBS



On Sat, Feb 14, 2015 at 4:49 PM, Igor Tandetnik  wrote:

> 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 product of DESCRIPTIONS to itself. I expect the two
> counts are the same, and are equal to a product of the actual, true counts.
>
> You are looking for something like this:
>
> select NAME,
>   sum(INSTR(FULL_TEXT, DEFINITION1) > 0),
>   sum(INSTR(FULL_TEXT, DEFINITION2) > 0),
> from Items join Descriptions
> group by NAME;
>
> -- or
>
> select NAME,
>   (select count(*) from Descriptions where INSTR(FULL_TEXT, DEFINITION1) >
> 0),
>   (select count(*) from Descriptions where INSTR(FULL_TEXT, DEFINITION2) >
> 0)
> from Items;
>
> The first one would probably work faster.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[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, Igor Tandetnik  wrote:

> 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 product of DESCRIPTIONS to itself. I expect the two
> counts are the same, and are equal to a product of the actual, true counts.
>
> You are looking for something like this:
>
> select NAME,
>   sum(INSTR(FULL_TEXT, DEFINITION1) > 0),
>   sum(INSTR(FULL_TEXT, DEFINITION2) > 0),
> from Items join Descriptions
> group by NAME;
>
> -- or
>
> select NAME,
>   (select count(*) from Descriptions where INSTR(FULL_TEXT, DEFINITION1) >
> 0),
>   (select count(*) from Descriptions where INSTR(FULL_TEXT, DEFINITION2) >
> 0)
> from Items;
>
> The first one would probably work faster.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[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 only one field, FULL_TEXT also a text
column.

This is the SQL that I think should work but doesn't as it takes very long
and produces
too high counts:

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

Leaving out the second join and only counting D.ROWID works all fine.
Must be overlooking something simple, but can't see it and thanks for any
advice.

RBS

[sqlite] GROUP BY with self join

2015-02-14 Thread Igor Tandetnik

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 product of DESCRIPTIONS to itself. I expect the 
two counts are the same, and are equal to a product of the actual, true 
counts.

You are looking for something like this:

select NAME,
   sum(INSTR(FULL_TEXT, DEFINITION1) > 0),
   sum(INSTR(FULL_TEXT, DEFINITION2) > 0),
from Items join Descriptions
group by NAME;

-- or

select NAME,
   (select count(*) from Descriptions where INSTR(FULL_TEXT, 
DEFINITION1) > 0),
   (select count(*) from Descriptions where INSTR(FULL_TEXT, 
DEFINITION2) > 0)
from Items;

The first one would probably work faster.
-- 
Igor Tandetnik

Re: [sqlite] group question

2014-10-18 Thread Igor Tandetnik


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 conditions on the 
underlying table rows, before aggregation is performed.


If you want to only report groups containing exactly three rows, use 
HAVING clause.

--
Igor Tandetnik

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

[sqlite] group question

2014-10-18 Thread ye yanhong

 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?

2014-09-26 Thread snowbiwan

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 x(x_id) 
); 

SELECT COALESCE(x_id, y_id) AS id 
  FROM y 
  LEFT JOIN x USING (x_id)
 GROUP BY id;



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/GROUP-BY-ambiguous-column-name-tp78282p78302.html
Sent from the SQLite mailing list archive at Nabble.com.
___
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?

2014-09-25 Thread James K. Lowden

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 elaborate on Cory Nelson's answer, GROUP BY is part of the SELECT
processing, and ORDER BY is not.  By analogy:

$ cat input | SELECT | ORDER BY > output

One way to understand it is that relational variables -- tables and
similar in SQL -- don't have order.  You can manipulate them
independent of order, using as many operators (JOIN, WHERE, etc.) as you
please.  Only when you're done can you ask the system, as a convenience
to yourself, to sort the results.  

SQLite takes some liberties with that model.  Depending on one's point
of view, supporting LIMIT and ORDER BY in a subquery is either a boon
or a wart.  Regardless, SQL as defined by the standard treats ORDER BY
differently, for the above reasons.  

--jkl
___
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?

2014-09-25 Thread Cory Nelson

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 2:02 PM, Mark Lawrence  wrote:

> 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 clever enough to recognize that a GROUP BY expression and a
> SELECT column are the same? Because in my mind I think of the query as
> working in the following stages for the most efficient operation:
>
> - JOIN ROWS
> - SELECT COLUMNS -- COALESCE done here
> - GROUP OUTPUT
> - ORDER OUTPUT
>
> However, it appears to be the case that the order is more like this:
>
> - JOIN ROWS
> - GROUP ROWS  -- COALESCE done here
> - SELECT COLUMNS -- COALESCE also done here?
> - ORDER OUTPUT
>
> Which looks to me like the expression would be calculated twice. Is
> SQLite smart enough to figure out that the columns are the same and
> only do it once?
>
> If SQLite is capable of determining that the same expression is used
> twice, why not just accept a SELECT expression?
>
> --
> Mark Lawrence
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Cory Nelson
http://int64.org
___
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?

2014-09-25 Thread Mark Lawrence

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 clever enough to recognize that a GROUP BY expression and a
SELECT column are the same? Because in my mind I think of the query as
working in the following stages for the most efficient operation:

- JOIN ROWS
- SELECT COLUMNS -- COALESCE done here
- GROUP OUTPUT
- ORDER OUTPUT

However, it appears to be the case that the order is more like this:

- JOIN ROWS
- GROUP ROWS  -- COALESCE done here
- SELECT COLUMNS -- COALESCE also done here?
- ORDER OUTPUT

Which looks to me like the expression would be calculated twice. Is
SQLite smart enough to figure out that the columns are the same and
only do it once?

If SQLite is capable of determining that the same expression is used
twice, why not just accept a SELECT expression?

-- 
Mark Lawrence
___
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?

2014-09-25 Thread Cory Nelson

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":
> >
> > SELECT
> > COALESCE(x.id, y.id) AS id
> > FROM
> > y
> > LEFT JOIN
> > x
> > ON
> > x.id = y.fk
> > ORDER BY
> > id
> > ;
>
> Sorry, that should read GROUP BY of course.
>
> --
> Mark Lawrence
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Cory Nelson
http://int64.org
___
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?

2014-09-25 Thread Mark Lawrence

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
> ;

Sorry, that should read GROUP BY of course.

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

[sqlite] GROUP BY: ambiguous column name?

2014-09-25 Thread Mark Lawrence

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) AS id
FROM
y
LEFT JOIN
x
ON
x.id = y.fk
ORDER BY
id
;

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
;

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. The syntax diagrams on the web page show the first as an
"ordering-term" and the second as an "expr" which doesn't enlighten me
much.

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

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

2013-12-22 Thread Keith Medcalf

>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 specific months.

This will only work for SUM aggregates where n (the count of values) does not 
affect the outcome.  

For other aggregates (such as the built-in AVG), you will get incorrect results 
using:

AVG(x * (y = 1))

because you will be adding "extra zero values" into the aggregate that ought 
not be there.  This applies to any aggregate function in which the number of 
observations is significant (average, standard deviation, variance, kurtosis, 
etc).  For these kinds of aggregates you will have to ensure that NULL is 
passed into (and ignored by) the aggregate function when the condition is not 
met:

AVG(CASE WHEN y = 1 THEN x ELSE NULL END)




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

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

2013-12-22 Thread RSmith

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 most programming languages, any Boolean
evaluated result can directly be inferred as the arithmetic values 0
and 1 so that I could make some programmatic assumptive statements
based

Yes, it's a bit of a trick.

Boolean logic operates on true and false values. Any two distinct
symbols can be used. It so happens SQLite apparently uses
1 and 0, but e.g. -1 and 0 or 'T' and 'F' would serve as well.

True, when a character interpretation is given, but when converted to a value, as in Integer Byte value, it is always 1 and 0 with
every compiler in existence - however this may not necessarily be the case in SQL (I will have to look up the Standard to actually
confirm this), but it _is_ the case with SQLite on any platform because the interpretation is subjective to the SQLite engine's
interpretation of parsed SQL and not the storage medium or processor of the target platform, as opposed to register bit-quantities
and the like, as far as I know.

Good point to notice or remind people of, when asking questions here and may
try to use similar methods on other SQL engines.

stats.quantity * (stats.which_month = 1)
is *logically* nonsensical: the product of a numerical quantity and a
a logical one. What would, say, "7 times false" mean?

"false false false false false false false"?

Haha, that is really ( false ^ 7 ), which is not true!

I wonder if ( true! ) = true...

( 7 * false ) is false - obviously... but ( 7 * True ) can still be false - if
you try to argue with my ex wife.

Cheers!
Ryan

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

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

2013-12-22 Thread RSmith



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 stats.which_month = 2 is TRUE



Kind of yes, although for more clarity I'd propose it stated like this:

For JAN: Results = SUM  ( value )  WHERE (
  value = (stats.quantity * _1_)  WHEN month is JAN
  value = (stats.quantity * _0_)  WHEN month is NOT JAN
)

For FEB: Results = SUM  ( value )  WHERE (
  value = (stats.quantity * _1_)  WHEN month is FEB
  value = (stats.quantity * _0_)  WHEN month is NOT FEB
)

etc.
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 specific months.


Apologies for the roundabout way of explaining - I never quite had to explain this specific kind of thing to anyone, it's usually a 
bit embedded - and I'm sure the same goes for most others on this forum!


Cheers,
Ryan






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

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

2013-12-22 Thread James K. Lowden

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 or 0.
> 
> For RDBMS and indeed most programming languages, any Boolean
> evaluated result can directly be inferred as the arithmetic values 0
> and 1 so that I could make some programmatic assumptive statements
> based

Yes, it's a bit of a trick.  

Boolean logic operates on true and false values.  Any two distinct
symbols can be used.  It so happens SQLite apparently uses
1 and 0, but e.g. -1 and 0 or 'T' and 'F' would serve as well.  

> stats.quantity * (stats.which_month = 1)

is *logically* nonsensical: the product of a numerical quantity and a
a logical one.  What would, say, "7 times false" mean?  

"false false false false false false false"?  

I'm not sure SQLite necessarily evaluates "x = y" as 0 or 1 for all
compilers and all architectures.  If it does, if SQLite is *defined* to
use 1 and 0 to represent true and false, and the SQL author is
indifferent to issues of portability (and, arguably, obscurity) then
the "number * boolean" construction might be attractive.  It is
certainly more compact.  

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

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

2013-12-22 Thread Luuk


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 '(stats.which_month = 1)' equals to 1 if the month is 
january and to 0 if the month is not january


so, this:
> SUM(stats.quantity * (stats.which_month = 1))

can be read as:
SUM(CASE WHEN stats.which_month = 1 THEN stats.quantity ELSE 0 END)



Am'I still in the darkness?


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

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 darkness?


On Sun, Dec 22, 2013 at 4:35 AM, RSmith  wrote:

> 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. It
> doesn't fit too well as a high-level storage value of a system even though
> it permeates low-level storage in every way -  it can't be null for
> instance, but in an RDBMS it _can_ be Null and it _can_ be arithmetic by
> taking on the values 0 and 1 for false and true respectively. This has the
> added advantage that it can be used in arithmetic statements which is what
> Igor used to save considerable amounts of typing in your query.
>
> Whether or not it is faster in execution than a case statement is probably
> up to some testing, but I am willing to bet it is somewhat faster
> considering the relative complexity of a CASE statement.
>
> Any statement containing a comparison operator ends up being a Boolean
> statement because it can be found to either be so, or not be so. Month is
> not Boolean, but (Month = January) is definitely Boolean, because the
> comparison is either true or false depending on the value of "Month".
>
> 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 most programming languages, any Boolean evaluated
> result can directly be inferred as the arithmetic values 0 and 1 so that I
> could make some programmatic assumptive statements based on it, such as:
>
> HumanX's fertility period recurs every (28 * (HumanX = Female)) days. This
> will be 0 days for men (28 * false) = (28 * 0) = 0, and 28 for ladies as
> (28 * true) = (28 * 1) = 28 in the same logic.
>
> Similarly your odds of balding is roughly 0.04 + (35/Age)*((HumanX = Male)
> * 10) since men have a roughly 40% chance of balding by age 35 which is
> about 10 times more likely than women and the odds increase with age[1].
>
>
> Hope this makes clear how Booleans are used mathematically in high-level
> code. Have a great day!
> Ryan
>
>
> [1] - http://www.statisticbrain.com/hair-loss-statistics/
>
>
>
> On 2013/12/21 22:24, Giuseppe Costanzi wrote:
>
>> ...but stats.which_month is not a boolean, it represent  a month...
>>
>>
>>
> ___
> 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] GROUP BY months and years using integer data

2013-12-21 Thread RSmith


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. It doesn't fit too well as a high-level storage value of a system even though it permeates low-level storage in every 
way -  it can't be null for instance, but in an RDBMS it _can_ be Null and it _can_ be arithmetic by taking on the values 0 and 1 
for false and true respectively. This has the added advantage that it can be used in arithmetic statements which is what Igor used 
to save considerable amounts of typing in your query.


Whether or not it is faster in execution than a case statement is probably up to some testing, but I am willing to bet it is 
somewhat faster considering the relative complexity of a CASE statement.


Any statement containing a comparison operator ends up being a Boolean statement because it can be found to either be so, or not be 
so. Month is not Boolean, but (Month = January) is definitely Boolean, because the comparison is either true or false depending on 
the value of "Month".


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 most programming languages, any Boolean evaluated result can directly be inferred as the arithmetic values 0 
and 1 so that I could make some programmatic assumptive statements based on it, such as:


HumanX's fertility period recurs every (28 * (HumanX = Female)) days. This will be 0 days for men (28 * false) = (28 * 0) = 0, and 
28 for ladies as (28 * true) = (28 * 1) = 28 in the same logic.


Similarly your odds of balding is roughly 0.04 + (35/Age)*((HumanX = Male) * 10) since men have a roughly 40% chance of balding by 
age 35 which is about 10 times more likely than women and the odds increase with age[1].



Hope this makes clear how Booleans are used mathematically in high-level code. 
Have a great day!
Ryan


[1] - http://www.statisticbrain.com/hair-loss-statistics/


On 2013/12/21 22:24, Giuseppe Costanzi wrote:

...but stats.which_month is not a boolean, it represent  a month...




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

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

2013-12-21 Thread Simon Slavin

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  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 "stats.which_month = 1" is a boolean and therefore
>> evaluates to either 0 or 1.

...but stats.which_month is not a boolean, it represent  a month...

However "(stats.which_month = 1)" is a boolean.

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

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
> >
> > ...SUM(stats.quantity * (stats.which_month = 1)) AS gen
> >
> > which is the meaning of the operator * in the statement?
>
> Multiply.
>
> It works because "stats.which_month = 1" is a boolean and therefore
> evaluates to either 0 or 1.
>
> Simon.
> ___
> 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] GROUP BY months and years using integer data

2013-12-21 Thread Simon Slavin

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 "stats.which_month = 1" is a boolean and therefore evaluates 
to either 0 or 1.

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

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
wrote:

> 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 default
>
> sqlite> create table T ( t int not null primary key );
> sqlite> insert into T values (1) ;
> sqlite> insert into T values (2) ;
> sqlite> .nullvalue NULL
> sqlite> select t, case t when 1 then 'one' end as tee from T;
> 1|one
> 2|NULL
>
> But I would recommend ELSE 0, on the principle that NULL arithmetic is
> dodgy at best.
>
> BTW, "which_month" means the same as "month"; you could tack
> "which" in front of any name without changing the meaning.
>
> --jkl
> ___
> 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] GROUP BY months and years using integer data

2013-12-21 Thread James K. Lowden

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 default

sqlite> create table T ( t int not null primary key );
sqlite> insert into T values (1) ;
sqlite> insert into T values (2) ;
sqlite> .nullvalue NULL
sqlite> select t, case t when 1 then 'one' end as tee from T;
1|one
2|NULL

But I would recommend ELSE 0, on the principle that NULL arithmetic is
dodgy at best.  

BTW, "which_month" means the same as "month"; you could tack
"which" in front of any name without changing the meaning.  

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

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

2013-12-21 Thread Igor Tandetnik


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 NULL END) AS
feb,


Or a bit more compact:

SELECT stats.which_year AS year,
SUM(stats.quantity * (stats.which_month = 1)) AS gen,
SUM(stats.quantity * (stats.which_month = 2)) AS feb,
...

--
Igor Tandetnik

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

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

2013-12-21 Thread RSmith

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 stats.which_month =  1  THEN stats.quantity ELSE NULL END) AS
gen,
SUM(CASE WHEN stats.which_month =  2  THEN stats.quantity ELSE NULL END) AS
feb,
SUM(CASE WHEN stats.which_month =  3  THEN stats.quantity ELSE NULL END) AS
mar,
SUM(CASE WHEN stats.which_month =  4  THEN stats.quantity ELSE NULL END) AS
apr,
SUM(CASE WHEN stats.which_month =  5  THEN stats.quantity ELSE NULL END) AS
mag,
SUM(CASE WHEN stats.which_month =  6  THEN stats.quantity ELSE NULL END) AS
giu,
SUM(CASE WHEN stats.which_month =  7  THEN stats.quantity ELSE NULL END) AS
lug,
SUM(CASE WHEN stats.which_month =  8  THEN stats.quantity ELSE NULL END) AS
ago,
SUM(CASE WHEN stats.which_month =  9  THEN stats.quantity ELSE NULL END) AS
sett,
SUM(CASE WHEN stats.which_month =  10  THEN stats.quantity ELSE NULL END) AS
ott,
SUM(CASE WHEN stats.which_month =  11  THEN stats.quantity ELSE NULL END) AS
nov,
SUM(CASE WHEN stats.which_month =  12  THEN stats.quantity ELSE NULL END) AS
dic,
stats.test_id AS test_id
FROM stats
WHERE stats.test_id = 420
GROUP BY which_year


Have a great day!
Ryan

On 2013/12/21 10:42, Giuseppe Costanzi wrote:

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 stats.which_month =  1  THEN SUM(stats.quantity) ELSE 0 END AS
gen,
CASE WHEN stats.which_month =  2  THEN SUM(stats.quantity) ELSE 0 END AS
feb,
CASE WHEN stats.which_month =  3  THEN SUM(stats.quantity) ELSE 0 END AS
mar,
CASE WHEN stats.which_month =  4  THEN SUM(stats.quantity) ELSE 0 END AS
apr,
CASE WHEN stats.which_month =  5  THEN SUM(stats.quantity) ELSE 0 END AS
mag,
CASE WHEN stats.which_month =  6  THEN SUM(stats.quantity) ELSE 0 END AS
giu,
CASE WHEN stats.which_month =  7  THEN SUM(stats.quantity) ELSE 0 END AS
lug,
CASE WHEN stats.which_month =  8  THEN SUM(stats.quantity) ELSE 0 END AS
ago,
CASE WHEN stats.which_month =  9  THEN SUM(stats.quantity) ELSE 0 END AS
sett,
CASE WHEN stats.which_month =  10  THEN SUM(stats.quantity) ELSE 0 END AS
ott,
CASE WHEN stats.which_month =  11  THEN SUM(stats.quantity) ELSE 0 END AS
nov,
CASE WHEN stats.which_month =  12  THEN SUM(stats.quantity) ELSE 0 END AS
dic,

stats.test_id AS test_id

FROM stats

WHERE  stats.test_id = 420
GROUP BY which_year


that return this

"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "71769" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "68972" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "65075" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "50605" "420"

if I add which_month in th e GROUP BY clause I've


"2009" "5994" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "6112" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "7046" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "5947" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "6471" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "6027" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "5841" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "3352" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "6564" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "7075" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "6283" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "5057" "420"
"2010" "6112" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "6201" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "6890" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "5907" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "6246" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "5667" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "5185" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "3269" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "5963" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "6520" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "6162" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "4850" "420"
"2011" "5888" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "6027" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "6756" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "5889" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "6276" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "5985" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "4968"

[sqlite] GROUP BY months and years using integer data