distinct and sum if like

Question

I have a table as the following

name
-----------
1@apple@1
2@apple@2
3@apple@4
4@box@4
5@box@5

and I want to get the result as:

name
--------------
apple 3
box   2

Thanks in advance for your help

I think you are looking for a COUNT DISTINCT: https://www.w3resource.com/sql/aggregate-functions/count-with-distinct.php — QuestionAsker, Jul 18 '19 at 07:37
What rdbms are you working with? Does the content always start with a single digit followed by an at sign and ends with an at sign followed by a single digit? Please read the first paragraph of the [sql tag info](https://stackoverflow.com/tags/sql/info) and [edit] your question accordingly. — Zohar Peled, Jul 18 '19 at 07:43
@Zohar Peled Sql server, no there is always different string (but same length) before and after the @ but I want to count just by the value in the middle — refaelo1979, Jul 18 '19 at 08:05

Ed Bangga · Answer 1 · 2019-07-18T08:16:20.693

2

This is what you need.

select
  SUBSTRING(
    name,
    CHARINDEX('@', name) + 1,
    LEN(name) - (
      CHARINDEX('@', REVERSE(name)) + CHARINDEX('@', name)
    )
  ),
  count(1)
from
  tbl
group by
  SUBSTRING(
    name,
    CHARINDEX('@', name) + 1,
    LEN(name) - (
      CHARINDEX('@', REVERSE(name)) + CHARINDEX('@', name)
    )
  )

edited Jul 18 '19 at 08:16

answered Jul 18 '19 at 07:53

Ed Bangga

12,879
4
16
30

It should be noted that this only works, if all the names follow the pattern described in the original post. – Jakob Busk Sørensen Jul 18 '19 at 07:56
`GROUP BY name` would also only group all _1@apple@1_ and _2@apple@2_ and so on. But the basic idea is good – schlonzo Jul 18 '19 at 07:58
Almost and only if OP is using SQL Server. But this is the first answer that assumes that the table contains anything else other then apples and boxes ;) – Lieven Keersmaekers Jul 18 '19 at 08:00
Perhaps you can include [this](http://sqlfiddle.com/#!18/c0468/4) in your answer. – Lieven Keersmaekers Jul 18 '19 at 08:01
updated to use REVERSE function to use pattern @{string}@, tried also this scenario @{string}@{char} – Ed Bangga Jul 18 '19 at 08:02
@Ed Bangga Id didn't sum all by the value in the middle, it did the substring and the count but not the sum for all rows having the same value in the middle – refaelo1979 Jul 18 '19 at 08:14
@refaelo1979, the group by should be able to handle it. – Ed Bangga Jul 18 '19 at 08:16
@Ed Bangga very well done my friend, though I got "Invalid length parameter passed to the LEFT or SUBSTRING function." as an error when trying to run on all the table but with where query for specific (like apple) did the job very well – refaelo1979 Jul 18 '19 at 08:28

Zaynul Abadin Tuhin · Answer 2 · 2019-07-18T07:46:37.177

1

use case when

select case when name like '%apple%' then 'apple'
           when name like '%box%' then 'box' end item_name,
count(*)
group by cas when name like '%apple%' then 'apple'
           when name like '%box%' then 'box' end

edited Jul 18 '19 at 07:46

answered Jul 18 '19 at 07:37

Zaynul Abadin Tuhin

31,407
5
33
63

@Zaynul Abadin Tuhin The table is too big than writing all cases, the question is if there is a way to count and sum by the value found between the @value@ – refaelo1979 Jul 18 '19 at 07:52
@Kaddath As far as I am aware MySQL is the only DBMS that lets you use a column alias in the `GROUP BY`. It is certainly not ANSI standard since the `GROUP BY` should be processes before the select, so I would not recommend using this proprietary syntax. – GarethD Jul 18 '19 at 07:53
@GarethD Oh right, i shouldn't mess up with questions where DBMS is not precised, MySql is what i'm most familiar with – Kaddath Jul 18 '19 at 07:55

GarethD · Accepted Answer · 2019-07-18T08:15:44.250

If your data does not contain any full stops (or periods depending on your vernacular), and the length of your string is less than 128 characters, then you can use PARSENAME to effectively split your string into parts, and extract the 2nd part:

DECLARE @T TABLE (Val VARCHAR(20));
INSERT @T (Val)
VALUES ('1@apple@1'), ('2@apple@2'), ('3@apple@4'), 
        ('4@box@4'), ('5@box@5');

SELECT  Val = PARSENAME(REPLACE(t.Val, '@', '.'), 2),
        [Count] = COUNT(*)
FROM    @T AS t
GROUP BY PARSENAME(REPLACE(t.Val, '@', '.'), 2);

Otherwise you will need to use CHARINDEX to find the first and last occurrence of @ within your string (REVERSE is also needed to get the last position), then use SUBSTRING to extract the text between these positions:

DECLARE @T TABLE (Val VARCHAR(20));
INSERT @T (Val)
VALUES ('1@apple@1'), ('2@apple@2'), ('3@apple@4'), 
        ('4@box@4'), ('5@box@5');

SELECT  Val = SUBSTRING(t.Val, x.FirstPosition + 1, x.LastPosition - x.FirstPosition),
        [Count] = COUNT(*)
FROM    @T AS t
        CROSS APPLY 
        (   SELECT  CHARINDEX('@', t.Val) ,
                    LEN(t.Val) - CHARINDEX('@', REVERSE(t.Val))
        ) AS x (FirstPosition, LastPosition)
GROUP BY SUBSTRING(t.Val, x.FirstPosition + 1, x.LastPosition - x.FirstPosition);

user14063792468 · Answer 4 · 2019-07-18T10:15:59.143

No DBMS specified, so here is a postgres variant. The query does use regexps to simplify things a bit.

with t0 as (
  select '1@apple@1' as value
  union all select '2@apple@2'
  union all select '3@apple@4'
  union all select '4@box@4'
  union all select '5@box@5'
),
trimmed as (
  select regexp_replace(value,'[0-9]*@(.+?)@[0-9]*','\1') as name
  from t0
)

select name, count(*)
from trimmed
group by name
order by name

DB Fiddle

Update

For Oracle DMBS, the query stays basically the same:

with t0 as (
  select '1@apple@1' as value from dual
  union all select '2@apple@2' from dual
  union all select '3@apple@4' from dual
  union all select '4@box@4' from dual
  union all select '5@box@5' from dual
),
trimmed as (
  select regexp_replace(value,'[0-9]*@(.+?)@[0-9]*','\1') as name
  from t0
)

select name, count(*)
from trimmed
group by name
order by name

NAME  | COUNT(*)
:---- | -------:
apple |        3
box   |        2

db<>fiddle here

Update

MySQL 8.0

with t0 as (
  select '1@apple@1' as value
  union all select '2@apple@2'
  union all select '3@apple@4'
  union all select '4@box@4'
  union all select '5@box@5'
),
trimmed as (
  select regexp_replace(value,'[0-9]*@(.+?)@[0-9]*','$1') as name
  from t0
)

select name, count(*)
from trimmed
group by name
order by name

name  | count(*)
:---- | -------:
apple |        3
box   |        2

db<>fiddle here

score 0 · Answer 5 · answered Jul 18 '19 at 07:44

0

You can use case and group by to do the same.

select new_col , count(new_col)
from
(
select case when col_name like '%apple%' then 'apple' 
when col_name like '%box%' then 'box'
else 'others' end new_col
from table_name
)
group by new_col
;

answered Jul 18 '19 at 07:44

Tajinder

2,248
4
33
54

The table is too big than writing all cases, the question is if there is a way to count and sum by the value found between the @value@ – refaelo1979 Jul 18 '19 at 07:51

distinct and sum if like

5 Answers5