CODEWITHSUNDEEP
sqlcount
Techno04335
Techno04335

Reputation: 1445

SQL: How can I combine a two separate values into one count

Scenario: I have a table Browsers which is populated every time a browser is opened along with the associated date:

BrowserName                Date
-------------------------------------
Firefox?                   8/20/2017
Firefox                    8/20/2017
Google Chrome              8/20/2017
Google Chrome              8/20/2017
Google Chrome              8/20/2017
Opera                      8/20/2017
Internet Explorer          8/20/2017
Internet Explorer          8/20/2017

If I did a count as such:

SELECT
    COUNT (BrowserName) AS [Count Of Uses],
    BrowserName AS [Browser],
    Date
FROM 
    BROWSERS
GROUP BY 
    Date, BrowserName

I get a result something like this:

Count Of Uses       Browser             Date
--------------------------------------------------
       1            Firefox?            8/20/2017
       1            Firefox             8/20/2017
       3            Google Chrome       8/20/2017
       2            Internet Explorer   8/20/2017
       1            Opera               8/20/2017

Question: notice how there are two entries for Firefox (Firefox and Firefox?). How can I combine those into one count for Firefox, so that my end result would be like this:

Count Of Uses       Browser             Date
--------------------------------------------------
       2            Firefox             8/20/2017
       3            Google Chrome       8/20/2017
       2            Internet Explorer   8/20/2017
       1            Opera               8/20/2017

Thanks.

Upvotes: 0

Views: 35

Answers (3)

Bohemian
Bohemian

Reputation: 425033

If you only need to handle this particular case:

SELECT
  COUNT (*) AS [Count Of Uses],
  replace(BrowserName, 'FireFox?', 'Firefox') AS [Browser],
  Date
FROM BROWSERS
GROUP BY 2,3

If you have multiple similar variations, nest replace() calls:

replace(replace(BrowserName, 'FireFox?', 'Firefox'), 'Internet Exploder', 'Internet Explorer') AS [Browser]

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

If you need to handle ? in general 

declare @T table (browser varchar(40), dt date);
insert into @T values 
      ('Firefox?', '8/20/2017')
    , ('Firefox', '8/20/2017')
    , ('Google Chrome', '8/20/2017')
    , ('Google Chrome', '8/20/2017')
    , ('Google Chrome?', '8/20/2017')
    , ('Opera', '8/20/2017')
    , ('Internet Explorer', '8/20/2017')
    , ('Internet Explorer',' 8/20/2017')
    , ('Internet Explorer', '8/21/2017')
    , ('Internet Explorer',' 8/21/2017');
with cte as 
( select t.dt, t.browser  
       , SUBSTRING(REVERSE(t.browser),1,1) as f 
       , case when SUBSTRING(reverse(t.browser), 1, 1) = '?' then substring(t.browser, 1, LEN(t.browser ) -1) else t.browser end as browserT
    from @T t 
) 
select cte.dt as [date], cte.browserT as browswer, count(*) as [count]
from cte
group by cte.dt, cte.browserT 
order by cte.dt, cte.browserT;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use case:

SELECT Date,
       (CASE WHEN BrowserName LIKE 'Firefox%' THEN 'Firefox'
             ELSE BrowserName
        END) as Browser,
       COUNT(*) AS [Count Of Uses]
FROM BROWSERS
GROUP BY Date,
         (CASE WHEN BrowserName LIKE 'Firefox%' THEN 'Firefox'
               ELSE BrowserName
          END) ;

Upvotes: 2

Related Questions