Re: SQL - Return count based on frequency of columns

[Alan Shore <AlanShore@xxxxxxxx>]

Hi Adam
this is completely untested, but it might point you in the right direction
With cr8file as (select Field1,
CASE WHEN
     Field2 = ' ' then 1 else 0 end as Fielda,
CASE WHEN
     Field3 = ' ' then 1 else 0 end as Fieldb,
CASE WHEN
     Field4 = ' ' then 1 else 0 end as Fieldc,
CASE WHEN
     Field5 = ' ' then 1 else 0 end as Fieldd,
CASE WHEN
     Field6 = ' ' then 1 else 0 end as Fielde,
CASE WHEN
     Field7 = ' ' then 1 else 0 end as Fieldf from filea)
Select Field1, Fielda + Fieldb + Fieldc + Fieldd + Fielde + Fieldf frpm
cr8file
order by 2, Field1


Note the order by
the 2 represents the 2 field that is being created
in this case
Fielda + Fieldb + Fieldc + Fieldd + Fielde + Fieldf



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill


                                                                       
             Adam Lang                                                 
             <raider.adam@gmai                                         
             l.com>                                                     To
             Sent by:                  Midrange Systems Technical      
             midrange-l-bounce         Discussion                      
             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>       
                                                                        cc
                                                                       
             02/24/2010 04:36                                      Subject
             PM                        SQL - Return count based on     
                                       frequency of columns            
                                                                       
             Please respond to                                         
             Midrange Systems                                          
                 Technical                                             
                Discussion                                             
             <midrange-l@midra                                         
                 nge.com>                                              
                                                                       
                                                                       




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.
Customer's 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?

Thanks.
Adam
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.