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