MySQL Sum and Case Query

Question

I create a ReportViewer with VB.NET connecting to a MySQL database. The data appears like below.

    IdProduct       Quantity       TotalPrice      OrderDate
    0001                1              10          29/09/2014
    0002                2              40          29/09/2014       
    0001                4              40          29/09/2014      
    0001                2              20          29/09/2014      
    0001                2              20          29/09/2014    

Based on the records above, I'd like the result to appear like below

   0001   0002  
    9      2
    90     40   

What is Query Sum Case the best use here? Thanks in advance.

Answer 1

NOTE: It's not possible for a query to "dynamically" alter the number or datatype of the columns returned, those must be specified at the time the SQL text is parsed.

To return the specified resultset with a query, you could do something like this:

SELECT SUM(IF(t.IdProduct='0001',t.Quantity,NULL)) AS `0001`
     , SUM(IF(t.IdProduct='0002',t.Quantity,NULL)) AS `0002`
  FROM mytable t
 UNION ALL
SELECT SUM(IF(t.IdProduct='0001',t.TotalPrice,NULL)) AS `0001`
     , SUM(IF(t.IdProduct='0002',t.TotalPrice,NULL)) AS `0002`
  FROM mytable t

Note that the datatypes returned by the two queries will need to be compatible. This won't be a problem if Quantity and TotalPrice are both defined as integer.

Also, there's no specific guarantee that the "Quantity" row will be before the "TotalPrice" row; we observe that behavior, and it's unlikely that it will ever be different. But, to have a guarantee, we'd need an ORDER BY clause. So, including an additional discriminator column (a literal in the SELECT list of each query), that would give us something we could ORDER BY.

Note that it's not possible to have this single query dynamically create another column for IdProduct '0003'. We'd need to add that to the SELECT list of each query.

We could do this in two steps, using a query to get the list of distinct IdProduct, and then use that to dynamically create the query we need.

---

BUT... with all that said... we don't want to do that.

The normative pattern would be to return Quantity and TotalPrice as two separate columns, along with the IdProduct as another column. For example, the result returned by this statement:

SELECT t.IdProduct
     , SUM(t.Quantity) AS `Quantity`
     , SUM(t.TotalPrice) AS `TotalPrice`
  FROM mytable t
 GROUP BY t.IdProduct

And then the client application would be responsible for transforming that resultset into the desired display representation.

We don't want to push that job (of transforming the result into a display representation) into the SQL.