title: DB2 SELECT statement using COUNT and GROUP BY
body: <p>I have a table in a DB2 database containing customer information that I need to retrieve along with a count of how many times a specific column in the table is duplicated. Here is an example of the data.</p>
  
  <pre><code>CUSTOMERID | CUSTOMERGROUP | PRODUCTID | PRODUCTNAME | ALERTNAME | ALERTLEVEL | XXX | YYY | ZZZ
  12345        ABC             987654      ProductA      Alert1      4            More  Data  Here
  </code></pre>
  
  <p>A customer is identified by the CustomerID and CustomerGroup columns. They can have any number of products and these products get different types of alerts (ProductA, ProductC and ProductQ could all get Alert1). I need to retrieve the customer information for each row along with a count of how many times that customer got a specific alertname.</p>
  
  <p>In our old MySQL database, this was not too difficult as I would do something like this</p>
  
  <pre><code>SELECT customerID, customerGroup, ProductID, ProductName, AlertName, count(AlertName), AlertLevel, more data....
  FROM TABLE
  WHERE customerID = XXX and customerGroup = YYY
  GROUP BY alertname
  ORDER BY AlertLevel, AlertName, ProductName
  </code></pre>
  
  <p>The group by did not include every column in the select statement so I would get back rows for the customer that included the customer information and a count of the number of times they received a specific alert.</p>
  
  <p>Now that we have migrated to DB2, I am required to put every column from the SELECT into the GROUP BY and this (obviously) makes each row distinct and therefore the count is now returning 1 for every row regardless of whether an alert name matches another row for this customer.</p>
  
  <p>Is there a way to recreate this functionality that will not require a significant overhaul of the way data is retrieved? As the developer of the front end, I have the ability to change the way I manipulate data on the PHP side and I can write SQL statements but I have no option for changing the way the data is stored.</p>