【Question】

I have database table as below:

I want to count the number of each product (ProductID) that have one or more attributes (AttID) and group them by ClassID.
The following SQL query is correct if I find the number of products that only has one of the attributes. To get the count of products having AttID 1, for example:

Java code

SELECT classid,COUNT(ProductID) AS Qty

FROM View\_mf\_ProductList WHERE (AttID=1)GROUP BY ClassID

classid   qty 

------------   ---------------- 

13       2 

14       1 

But I want to find those having more than one attribute, such as AttID=1 & AttID=2, so the above SQL is wrong.
Below is the desired output. Could anyone give a correct solution in SQL? Thanks.

classid  qty 

------------   ---------------- 

13      1 

14      0

【Answer】

This needs to first group records and then group each subgroup to perform aggregate over each sub-subgroup. It’s much simpler to retrieve data out from the database to process data in SPL. The Structured Process Language needs a one-liner to get it done:

A1: Retrieve data from the database table with a SQL-like statement.

A2: Group records by ClassID and then group each subgroup by ProductID and count the number of sub-subgroups that hold two members.