Dealing with comma separated value in group by clause

Question

I have a column named purchasetype which contains three kind of values new,trial,new,trial another column in noofsales
I am counting the total number based on the purchasetype
The query I am using is

   Select SUM(noofsales) 
     From table 
  GroupBy purchasetype

This query is giving me 3 kind of sum result new,trial and new,trial
I want total sum in new and trial only if one record contains new,trial then one count should increase for new and also one count should increase for trial
I have tried using substring_index function like

Select SUM(noofsales) 
     From table 
  Groupby substring_index(PurchaseType, ',', 0)

but this is summing up only for the first record in comma separated value.

use sum(if(condition, 1, 0)), you have examples here https://stackoverflow.com/questions/13075505/mysql-is-it-possible-to-sum-if-or-to-count-if — Zeljka, Jan 22 '19 at 11:29

score 0 · Answer 1 · answered Jan 22 '19 at 11:19

0

In your case you can use CASE/WHEN statement for each sum. For example:

SELECT query with CASE condition and SUM()

answered Jan 22 '19 at 11:19

stepozer

1,143
1
10
22

purchase type is having so many values, I have just given a simple example – Payal Mondal Jan 22 '19 at 11:27
Can't we do it by changing the group by clause? – Payal Mondal Jan 22 '19 at 12:04

score 0 · Answer 2 · answered Jan 22 '19 at 12:49

You could create a number table on the fly, and use it to turn records where purchase_type is a comma separated value into new rows. Then in the outer query you can aggregate properly.

Consider the following query, that you can see in action in this db fiddle. It will dynamically discover the available purchase types and generate the result that you expect :

SELECT x.purchase_type, SUM(x.noofsale)
FROM (
    SELECT
        t.noofsale,
        SUBSTRING_INDEX(SUBSTRING_INDEX(t.purchase_type, ',', numbers.n), ',', -1) purchase_type
    FROM
        (SELECT 1 n UNION SELECT 2) numbers 
        INNER JOIN t ON CHAR_LENGTH(t.purchase_type) - CHAR_LENGTH(REPLACE(t.purchase_type, ',', ''))>= numbers.n-1
) x
GROUP BY x.purchase_type
ORDER BY 1,2

NB : this query supports maximum two comma-separated values (as shown in your sample data) ; if you expected more than that, you may adapt the numbers subquery :

(SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ...)

Dealing with comma separated value in group by clause

2 Answers2