Re: SQL - Return count based on frequency of columns

[CRPence <CRPbottle@xxxxxxxxx>]

Adam Lang wrote:
> Here is a database\query problem I have been trying to wrap my
> head around for a couple days.
>
> I have 7 columns.  The first column is unique with someone's
> name. The next 6 columns are products that could have been
> purchased. Customers could have been marked as having anywhere
> from 0 to all 6 fields tagged (not null).
>
> Returning all the records where no fields or all 6 fields are not
> null is easy.  The problem I am having is doing 1 through 5
> without a crazy amount of where statements.
>
> So basically, I would like to return all customer names that have
> 1 of the 6 columns not empty, all customers that have 2 of 6 not
> empty.  3 of 6, etc.
>
> I have run out of ideas and figured to try you guys for ideas.
> Any hints or ideas?  Or is this something not to be done via SQL?

  A couple ideas with a setup [that attempts to represent somewhat, 
what is stated in words versus scripted] preceding the queries.  For 
obviousness of the example data, customer N has N non-null values. 
Two sample SELECT are given, each requesting "4 of 6" product 
purchases.  The example with the CTE should be able to be 
encapsulated in a VIEW like with the pivoted result.  So although 
the problem can be solved by SQL, that solutions are not very 
obvious, should be an indication that the data is not defined in a 
relational manner.

<code>
  create table custpurch
  (cust int not null /* name as integer for ease */
  , primary key (cust)
  ,p1 int default null /* purch 1 */
  ,p2 int default null /* purch 2 */
  ,p3 int default null /* purch 3 */
  ,p4 int default null /* purch 4 */
  ,p5 int default null /* purch 5 */
  ,p6 int default null /* purch 6 */
  );
  insert into  custpurch values
   (1,   11, null, null, null, null, null)
  ,(2,   11,   22, null, null, null, null)
  ,(3, null,   22,   77,   33, null, null)
  ,(4,   11,   22, null, null,   88,   99)
  ,(0, null, null, null, null, null, null)
  ;
  -- count non-null values with CASE and addition
  with
    cnn (cust, cnt) as
      (select cust
            , case when p1 is null then 0 else 1 end
            + case when p2 is null then 0 else 1 end
            + case when p3 is null then 0 else 1 end
            + case when p4 is null then 0 else 1 end
            + case when p5 is null then 0 else 1 end
            + case when p6 is null then 0 else 1 end
       from custpurch
       )
  select * from cnn
  where cnt=4
  ;
  -- output from above select
              CUST             CNT
                 4               4
     ********  End of data  ********
  -- or above w/out WHERE, adding ORDER BY cnt DESC
              CUST             CNT
                 4               4
                 3               3
                 2               2
                 1               1
                 0               0
     ********  End of data  ********
  -- "pivot" the table to make the columns into rows:
  create view custpurchvw (cust, p) as
     (            select cust, p1 from custpurch
       union all  select cust, p2 from custpurch
       union all  select cust, p3 from custpurch
       union all  select cust, p4 from custpurch
       union all  select cust, p5 from custpurch
       union all  select cust, p6 from custpurch
     )
  ;
  -- count(p) counts non-null values
  select cust,count(p) as cnt
  from custpurchvw
  group by cust
  having count(p)=4
  ;
  -- output from above select
              CUST             CNT
                 4               4
     ********  End of data  ********
  -- not very obvious; count 1-byte for each non-null
  -- by appending an empty string for a 0-byte count
  select length( ifnull(left(p1,1),'')
          concat ifnull(left(p2,1),'')
          concat ifnull(left(p3,1),'')
          concat ifnull(left(p4,1),'')
          concat ifnull(left(p5,1),'')
          concat ifnull(left(p6,1),'')
               ), c.*
    from custpurch c
</code>

Regards, Chuck