CODEWITHSUNDEEP
sqlsql-server-2008
user1130511
user1130511

Reputation: 253

SQL multiple conditions for group of data

I am trying to write a validation for the following set of data:

SSYS | Material_Number | Characteristic | Description
001  | 000000000001111 | SH_DESC        | TEST
001  | 000000000001111 | DESIGN_TYPE    | NULL
001  | 000000000001111 | VOLTAGE        | NULL
001  | 000000000009999 | SH_DESC        | TEST2
001  | 000000000009999 | OPER_METHOD    | LIGHT
001  | 000000000009999 | FILTER_TYPE    | Filter element,Air
001  | 000000000014560 | SH_DESC        | Horn,Signal
001  | 000000000014560 | DIMENSION_SIZE | NULL
001  | 000000000014560 | FILTER_TYPE    | NULL

I would like to group by the Material_Number and count as 1 (ie. true) if within the Material_Number group, the SH_DESC description is NOT NULL and all other characteristics' descriptions IS NULL. So, in this case my result would be:

SSYS | Material_Number | Characteristic | Description  | COUNT
001  | 000000000001111 | SH_DESC        | TEST         |  1
001  | 000000000009999 | SH_DESC        | TEST2        |  0 
001  | 000000000014560 | SH_DESC        | Horn,Signal  |  1

My attempt:

Select COUNT (*), SSYS, Material_Number, Characteristic, Description
 From myDB where (Characteristic = 'SH_DESC' AND DESCRIPTION IS NOT NULL) AND (Characteristic NOT IN ('SH_DESC') IS NULL)
GROUP BY SSYS, Material_Number, Characteristic, Description HAVING COUNT (*) < 2

Any help is much appreciated!

Upvotes: 0

Views: 9075

Answers (4)

AnandPhadke
AnandPhadke

Reputation: 13486

Try this -- Here I guess you cant get the description bcos there is nothiing to filter the specific description.

CREATE TABLE yourtable(SSYS varchar(10),Material_Number varchar(100),Characteristic varchar(100),Description varchar(100))

INSERT INTO yourtable 
VALUES('001','000000000001111','SH_DESC','TEST'),
('001','000000000001111','DESIGN_TYPE','NULL'),
('001','000000000001111','VOLTAGE','NULL'),
('001','000000000009999','SH_DESC','TEST2'),
('001','000000000009999','SH_DESC','LIGHT'),
('001','000000000009999','FILTER_TYPE','Filter element,Air'),
('001','000000000014560','SH_DESC','Horn,Signal'),
('001','000000000014560','DIMENSION_SIZE','NULL'),
('001','000000000014560','FILTER_TYPE  ','NULL')

select max(SSYS),
       max(Material_Number),
       'SH_DESC' as Characteristic,
       CASE WHEN SUM(CASE WHEN Characteristic='SH_DESC' and Description is not null then 1 else 0 end) = 1 then 1 else 0 end  as cnt
from yourtable
group by Material_Number

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

An alternative to the GROUP BY and SUM(CASE WHEN) options...

 SELECT
  *,
  CASE WHEN Description IS NULL THEN 0
       WHEN EXISTS (SELECT *
                      FROM myDB as lookup
                     WHERE lookup.SSYS            = myDB.SSYS
                       AND lookup.Material_Number = myDB.Material_Number
                       AND lookup.Characteristic <> 'SH_DESC'
                       AND lookup.Description    IS NOT NULL) THEN 0
       ELSE 1 END as myCount
FROM
  myDB
WHERE
  Characteristic = 'SH_DESC'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Try this:

select ssys, material_number, 'SH_DESC' as characteristic,
       (case when sum(case when characteristic is not null and characteristic<> 'SH_DESC' and description is null then 1 else 0 end) = count(*) - 1
             then 1
             else 0
        end) as count
from t
group by ssys, material_number

It groups by material and counts the number of rows that have non-null characterist where the description is null. It sets count accordingly.

Upvotes: 0

user359040
user359040

Reputation:

Try:

Select SSYS, 
       Material_Number, 
       'SH_DESC' Characteristic, 
       MAX(CASE WHEN Characteristic = 'SH_DESC' THEN Description END) Description,
       CASE WHEN MAX(CASE WHEN Characteristic = 'SH_DESC' THEN Description END) IS NOT NULL AND
                 MAX(CASE WHEN Characteristic <>'SH_DESC' THEN Description END) IS NULL 
            THEN 1
            ELSE 0
       END COUNT
From myDB
GROUP BY SSYS, Material_Number

Upvotes: 3

Related Questions