How to combine and count multiple rows with different values using SQL

Question

I've seen multiple scenarios where you combine multiple rows with the same value, but haven't been able to locate anything where you can combine multiple rows with different values. If I have a table with source and accompanying date, I want to get a count of the dates for each source and then group source by a select few values. Example below:

Current

  Source                Count
  Yahoo                 10
  Bing                  15
  Google                12
  Paid                  10
  Organic               15

Needed

  Source                Count
  Media                 37
  Paid                  10
  Organic               15

use a case statement if you have no other grouping and hard coding is acceptable. — xQbert
– xQbert, Commented Jul 20, 2015 at 19:21

xQbert · Accepted Answer · 2015-07-20 19:29:26Z

1

SELECT case when source in ('Yahoo','Bing','Google') then 'Media' 
            else Source end as Source,  sum(count) as count
GROUP BY case when source in ('Yahoo','Bing','Google') then 'Media' 
            else Source end

edited Jul 20, 2015 at 19:29

answered Jul 20, 2015 at 19:23

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2015-07-20 19:22:45Z

0

select case when source not in ('Paid','Organic') 
            then 'Media'
            else source
       end as my_source, 
       count(*)
from your_table
group by my_source

answered Jul 20, 2015 at 19:22

juergen d

205k40 gold badges303 silver badges377 bronze badges

Comments

ImCrimson · Accepted Answer · 2015-07-20 19:46:35Z

0

Most of what people have put is correct. But they have gotten alittle confused because of the 'count' they are thinking of it as a function not as a variable. By your data, you're measuring and grouping marketing sources.

Here is the query you need.

    select 
case 
when Source  in ('Yahoo','Bing','Google') then 'Media'
else Source
end as 'Source',
case 
when Source  in ('Yahoo','Bing','Google') then (Select sum(Count) from your_table where source in  in ('Yahoo','Bing','Google'))
else Count
end as 'Count'
from your_table
group by 'Source'

answered Jul 20, 2015 at 19:46

ImCrimson

1461 gold badge1 silver badge11 bronze badges

Comments

SQLMonger · Accepted Answer · 2015-07-20 20:13:53Z

0

This method is a slight improvement over the other methods posted in that it puts the logic for your grouping into an outer apply, and uses the "" operator so that it refers back to the base query. This allows for the logic to be stated and maintained in one place, making the code easier to read as well. Not sure if MySQL supports the "" operator, but worth a try...

select [Source Group], 
       count(*)
from your_table as S
outer apply (select case when s.source not in ('Paid','Organic') 
            then 'Media'
            else s.source
            end 
        as [Source Group])_ 
group by [Source Group]

answered Jul 20, 2015 at 20:13

SQLMonger

3003 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to combine and count multiple rows with different values using SQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related