Re: SQL - Return count based on frequency of columns

Hi Adam
Reverse the 0 and the 1 for each of the case
This message was sent from my BlackBerry
My BlackBerry number is 631 880 8640


----- Original Message -----
From: Alan Shore [AlanShore@xxxxxxxx]
Sent: 02/24/2010 04:52 PM EST
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: SQL - Return count based on frequency of columns




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.