CODEWITHSUNDEEP
mysqlsql
ntc
ntc

Reputation: 257

sql - group by parts of a string in sql

I'm using sql to pull together a table like this:

person         flavour                                purchase
andy         likes-orange_hates-vanilla                  $5
george       likes-orange_hates-icecream                 $5
gena         likes-vanilla_hates-bourban                 $2
gena         likes-vanilla_hates-bourban                 $4

Is it possible to go from that to:

                total
likes-orange     $10
likes-vanilla    $6

even better would be

              total      av. purchase size     av. purchases per person    total people
likes-orange    $10                $5                   1                        2
likes-vanilla   $6                 $3                   2                        1

I just have access to a phpmyadmin.

Upvotes: 4

Views: 8755

Answers (2)

Ashish Diwakar
Ashish Diwakar

Reputation: 1

In SQL 2005+

Following can be used :

Select Tbl1.Email, Count(1) FROM (
    Select substring(EmailAdd,CHARINDEX('@',EmailAdd),Len(EmailAdd)-CHARINDEX('@',EmailAdd)) as 'Email'
    from TableName 
) Tbl1 Group By Tbl1.Email

Upvotes: 0

Jim Garrison
Jim Garrison

Reputation: 86764

Try

select substring_index(flavor,'_',1), sum(purchase) from table group by 1

Upvotes: 12

Related Questions