Question 314254 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Create Custom Column based on Account Type

I’m trying to use a SQL query to build a custom field that will identify each account as Search or PMax or Both (Search & PMax).

Please help

Thank you.
Santosh.

enter image description here

Here is rawdata and expected output in the attached image.

Here is the query i have written to achieve this desired output, but not getting it right.

SELECT
         "Testing"."Date" AS 'DATE',
         "Testing"."Account Name" AS 'ACCOUNT',
         "Testing"."Type" AS 'TYPE',
         (
            CASE
                 WHEN "Testing"."Type"  = 'Search'
                 AND    "Testing"."Spend"  > 0
                 AND    "Testing"."Account Name"  IS NOT NULL THEN 'Search'
                 WHEN "Testing"."Type"  = 'Pmax'
                 AND    "Testing"."Spend"  > 0
                 AND    "Testing"."Account Name"  IS NOT NULL THEN 'Pmax'
                 WHEN "Testing"."Type"  = 'Search'
                 AND    "Testing"."Type"  = 'Pmax'
                 AND    "Testing"."Spend"  > 0
                 AND    "Testing"."Account Name"  IS NOT NULL THEN 'Search & Pmax'
                 ELSE 'NA'
             END) AS 'Type_new',
         SUM("Testing"."Spend") AS 'SPEND'
FROM  "Testing" 
GROUP BY "Testing"."Date",
     "Testing"."Account Name",
     "Testing"."Type",
      (
        CASE
             WHEN "Testing"."Type"  = 'Search'
             AND    "Testing"."Spend"  > 0
             AND    "Testing"."Account Name"  IS NOT NULL THEN 'Search'
             WHEN "Testing"."Type"  = 'Pmax'
             AND    "Testing"."Spend"  > 0
             AND    "Testing"."Account Name"  IS NOT NULL THEN 'Pmax'
             WHEN "Testing"."Type"  = 'Search'
             AND    "Testing"."Type"  = 'Pmax'
             AND    "Testing"."Spend"  > 0
             AND    "Testing"."Account Name"  IS NOT NULL THEN 'Search & Pmax'
             ELSE 'NA'
         END)
Tags:

2

Answers


  1. 0

    You need to aggregate the table using GROUP BY and compute the resulting columns in each group.

    For example, you can do:

    select
  min(date) as date,
  account,
  case when min(account_type) = max(account_type) 
       then min(account_type)
       else concat(max(account_type), ' & ', min(account_type)) as account_type,
  sum(spend) as spend
from t
group by account

    If there are more than two types, then you could use GROUP_CONCAT().

    Login or Signup to reply.
  2. 0

    You can use GROUP BY and conditions as follows:

    select date, account,
       case when count(distinct account_type) = 2 
            then 'Search & Pmax'
            else max(account_type)
       end as account_type,
       sum(spend) as spend
  from your_table
group by date, account
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search