SQL question, move union to second column

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL question, move union to second column

Bart Smissaert
Have 2 tables:

PERSONS:
CREATE TABLE [PERSONS]([ID] INTEGER PRIMARY KEY, [Place] TEXT)

ATTENDED:
CREATE TABLE [ATTENDED]([ID] INTEGER)

Sample date like this:

PERSONS:

ID Place
-----------
1 A
2 A
3 B
4 A
5 A
6 A
7 B
8 B
9 A
10 A

ATTENDED:

ID
---------
1
5
6
1
1
8
9
5
1
5
8
1
6
8
9
9
1
5
6
1

Now I would like to show the counts of persons that not attended, grouped
by place and in a third column the counts of all persons, again grouped by
place.

I can do it differently in 2 columns with a union:

SELECT P.PLACE, COUNT(P.ID) AS P_COUNT FROM PERSONS P LEFT JOIN ATTENDED A
ON(P.ID = A.ID)
WHERE A.ID IS NULL GROUP BY P.PLACE
UNION ALL
SELECT PLACE, COUNT(ID) AS P_COUNT FROM PERSONS
GROUP BY PLACE

But I would like the result to be in 3 columns, so result in this case
would be:

Place Not_Attended All
-------------------------------
A       3                      7
B       2                      3

Probably simple, but I can't work it out and thanks for any assistance.

RBS
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL question, move union to second column

Igor Tandetnik-2
On 3/13/2019 8:08 PM, Bart Smissaert wrote:
> But I would like the result to be in 3 columns, so result in this case
> would be:
>
> Place Not_Attended All
> -------------------------------
> A       3                      7
> B       2                      3

Something like this (not tested):

select PLACE,
   sum(ID not in (select ID from ATTENDED)) Not_Attended,
   count(*) All
from PERSONS group by PLACE;

--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL question, move union to second column

Bart Smissaert
Thanks; this works fine:

select PLACE,
    sum(ID not in (select ID from ATTENDED)),
    count(ID)
 from PERSONS group by PLACE

But if I add the aliases I get:

near "All": syntax error
Result of sqlite3_prepare16_v3: 1
select PLACE, sum(ID not in (select ID from ATTENDED)) Not_Attended,
count(*) All from PERSONS group by PLACE

Otherwise very neat indeed though!

RBS



On Thu, Mar 14, 2019 at 12:21 AM Igor Tandetnik <[hidden email]> wrote:

> On 3/13/2019 8:08 PM, Bart Smissaert wrote:
> > But I would like the result to be in 3 columns, so result in this case
> > would be:
> >
> > Place Not_Attended All
> > -------------------------------
> > A       3                      7
> > B       2                      3
>
> Something like this (not tested):
>
> select PLACE,
>    sum(ID not in (select ID from ATTENDED)) Not_Attended,
>    count(*) All
> from PERSONS group by PLACE;
>
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL question, move union to second column

Bart Smissaert
Sorry, ignore that, can see now that all is a reserved word.

RBS

On Thu, Mar 14, 2019 at 12:30 AM Bart Smissaert <[hidden email]>
wrote:

> Thanks; this works fine:
>
> select PLACE,
>     sum(ID not in (select ID from ATTENDED)),
>     count(ID)
>  from PERSONS group by PLACE
>
> But if I add the aliases I get:
>
> near "All": syntax error
> Result of sqlite3_prepare16_v3: 1
> select PLACE, sum(ID not in (select ID from ATTENDED)) Not_Attended,
> count(*) All from PERSONS group by PLACE
>
> Otherwise very neat indeed though!
>
> RBS
>
>
>
> On Thu, Mar 14, 2019 at 12:21 AM Igor Tandetnik <[hidden email]>
> wrote:
>
>> On 3/13/2019 8:08 PM, Bart Smissaert wrote:
>> > But I would like the result to be in 3 columns, so result in this case
>> > would be:
>> >
>> > Place Not_Attended All
>> > -------------------------------
>> > A       3                      7
>> > B       2                      3
>>
>> Something like this (not tested):
>>
>> select PLACE,
>>    sum(ID not in (select ID from ATTENDED)) Not_Attended,
>>    count(*) All
>> from PERSONS group by PLACE;
>>
>> --
>> Igor Tandetnik
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL question, move union to second column

Igor Tandetnik-2
On 3/13/2019 8:32 PM, Bart Smissaert wrote:
> Sorry, ignore that, can see now that all is a reserved word.

You can enclose it in double quotes, as in "All", if you really want it.
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL question, move union to second column

Bart Smissaert
Ah, yes, thanks.

RBS

On Thu, Mar 14, 2019 at 12:55 AM Igor Tandetnik <[hidden email]> wrote:

> On 3/13/2019 8:32 PM, Bart Smissaert wrote:
> > Sorry, ignore that, can see now that all is a reserved word.
>
> You can enclose it in double quotes, as in "All", if you really want it.
> --
> Igor Tandetnik
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users