Is it possible to "Group by" by column content?

Asked

Viewed 55 times

2

I was wondering if it was possible to use GROUP BY based on the date of a column, in this case the column ANALYSIS that creates groups whenever it finds 0, instead of the column.

       DIA        MES       YEAR      TODAY   TOMORROW   ANALYSIS      LIMIT
---------- ---------- ---------- ---------- ---------- ---------- ----------
        19          9       2016        111        988          0        150 
        20          9       2016        988        853        853        150 
        21          9       2016        853        895        895        150 
        22          9       2016        895        776        776        150 
        23          9       2016        776        954          0        150 
        26          9       2016        954        968        968        150 
        27          9       2016        968        810        810        150 
        28          9       2016        810        937        937        150 
        29          9       2016        937        769        769        150 
        30          9       2016        769       1020          0        150 
         3         10       2016       1020        923        923        150 
         4         10       2016        923         32         32        150

What I wanted was the following, based on this example:

  • Group 1 (Analysis): 0
  • Group 2(Analysis): 853, 895,776,0
  • Group 3(Analysis): 968,810,937,169,0
  • ...
mysql sql sql-server oracle

  • Please translate your question into English.

  • ups, I thought I was in English version, my xd error

  • Is it Mysql or SQL Server? If it is SQL Server, which is the version?

  • oracle version 4.0.3.16(so I say possibly sql server) however what matters is the reasoning

  • "4.0.3" probably refers to the Oracle SQL Developer, which is not the database manager but the Oracle IDE (Integrated Development Environment).

  • If I’m not mistaken Sql Developer uses Sql server, however I don’t know the version

  • I think not even with "Analytic functions" because the "break" is complicated. How would the output be used ? Report ?

  • The simplest way to implement what you request is by using cursor. // Another option is to adapt the algorithm gaps and Islands: sequence the lines using YEAR/MES/DIA as ordering criteria (so there is no interval), then remove the lines with ANALYSIS = 0 and after searching for the ranges.

  • Gaps and Islands looks promising

  • Or try to combine lad and lead with listagg https://oracle-base.com/articles/misc/analytic-functions try to get the last zero.

Show 5 more comments

1 answer

0

Using the "start-of-group" method it is possible to make a kind of iterator.

select 
from   ( select tbl.*,
                count(case when analysis = 0 then 1 end) 
                           over (order by year, mes, dia) as cnt
         from   tbl
       )
where  ...
GROUP BY cnt;

This code was not made by me. so a big thank you to Mathguy.

Browser other questions tagged mysql sql sql-server oracle

You are not signed in. Login or sign up in order to post.