select id,
entry_date,
max(case when term == 'Systolic' then reading end) as Systolic,
max(case when term == 'Diastolic' then reading end) as Diastolic
from the_table
group by id, entry_date
;
should be
select id,
entry_date,
max(case when term == 'Systolic' then reading end)
|| '/' ||
max(case when term == 'Diastolic' then reading end) as Pressure
from the_table
group by id, entry_date
;
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <[email protected]> On
>Behalf Of Keith Medcalf
>Sent: Sunday, 9 February, 2020 19:17
>To: SQLite mailing list <[email protected]>
>Subject: Re: [sqlite] How to group this?
>
> select id,
> entry_date,
> max(case when term == 'Systolic' then reading end) as Systolic,
> max(case when term == 'Diastolic' then reading end) as Diastolic
> from the_table
>group by id, entry_date
>;
>
>If you want to make sure you have both terms for a given id/entry_date
>then you need to make it a subquery so you can check for null.
>
>select id,
> entry_date,
> systolic || '/' || diastolic as pressure
> from (
> select id,
> entry_date,
> max(case when term == 'Systolic' then reading end) as
>Systolic,
> max(case when term == 'Diastolic' then reading end) as
>Diastolic
> from the_table
> group by id, entry_date
> )
> where systolic is not null
> and diastolic is not null
>;
>
>Note that (id, entry_date, term) needs to be unique ... or you will just
>get the combination of the max(systolic)/max(diastolic) for the results
>for a given id/entry_date.
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-----Original Message-----
>>From: sqlite-users <[email protected]> On
>>Behalf Of Bart Smissaert
>>Sent: Sunday, 9 February, 2020 17:25
>>To: General Discussion of SQLite Database <sqlite-
>>[email protected]>
>>Subject: [sqlite] How to group this?
>>
>>ID ENTRY_DATE TERM NUMERIC_VALUE ROWID
>>----------------------------------------------------------------
>>1308 15/Mar/2013 Systolic 127 701559
>>1308 15/Mar/2013 Diastolic 81 701568
>>1308 27/Jun/2013 Systolic 132 701562
>>1308 27/Jun/2013 Systolic 141 701563
>>1308 27/Jun/2013 Systolic 143 701564
>>1308 27/Jun/2013 Diastolic 82 701571
>>1308 27/Jun/2013 Diastolic 85 701572
>>1308 27/Jun/2013 Diastolic 94 701573
>>278975701 08/Mar/2018 Systolic 136 1583551
>>278975701 08/Mar/2018 Diastolic 99 1583591
>>278975701 04/Apr/2018 Systolic 119 1583552
>>278975701 04/Apr/2018 Systolic 124 1583553
>>278975701 04/Apr/2018 Systolic 130 1583554
>>278975701 04/Apr/2018 Diastolic 74 1583592
>>278975701 04/Apr/2018 Diastolic 75 1583593
>>278975701 04/Apr/2018 Diastolic 85 1583594
>>
>>These are systolic and diastolic blood pressures for 2 people with the
>>ID's
>>1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc.
>>Systolic and diastolic values are a pair and should be grouped in one
>>row.
>>This is no problem if there is only one pair for one date, but sometimes
>>there multiple pairs per date.
>>The pairing should be based on the rowed if there are multiple pairs by
>>date, so for ID 1308
>>I should get:
>>
>>127/81
>>132/82
>>141/85
>>143/94
>>
>>What should be the SQL to group like this?
>>
>>RBS
>>_______________________________________________
>>sqlite-users mailing list
>>[email protected]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
