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
midrange.com
