CODEWITHSUNDEEP
sqlsql-server-2008
PriceCheaperton
PriceCheaperton

Reputation: 5349

Grouping By different rows

I have returned rows which look like this:

2 - Eggs
3 - Bacon
4 - Bacon Smoked

I would like to group by '%Bacon%' so that my count is 2.

How can i do this is SQL?

I should see results like this:

Eggs - 1 Bacon - 2

Upvotes: 1

Views: 64

Answers (5)

Ruslan Veselov
Ruslan Veselov

Reputation: 337

Other solution:

 SELECT 
    Eggs = SUM(CASE WHEN FoodColumn LIKE '%Eggs%' THEN 1 ELSE 0 END),
    Bacon = SUM(CASE WHEN FoodColumn LIKE '%Bacon%' THEN 1 ELSE 0 END)
 FROM Test

You can see demo here.

If you need to separate the result into two separate rows

 SELECT *
 FROM
 (
    SELECT 
       Eggs = SUM(CASE WHEN FoodColumn LIKE '%Eggs%' THEN 1 ELSE 0 END),
       Bacon = SUM(CASE WHEN FoodColumn LIKE '%Bacon%' THEN 1 ELSE 0 END)
    FROM Test 
 ) AS Test
 UNPIVOT
 (
    Quantity FOR Foods IN (Eggs, Bacon)
 ) AS Result

You can see demo here.

Upvotes: 1

Andrew
Andrew

Reputation: 7768

Not tested, but I think it should work

SELECT COUNT(*) as QTY, RS.FOOD_TYPE
FROM 
(SELECT 
Case patIndex ('%[ /-]%', LTrim (FOOD_TYPE))
        When 0 Then LTrim (FOOD_TYPE)
        Else substring (LTrim (FOOD_TYPE), 1, patIndex ('%[ /-]%', LTrim (FOOD_TYPE)) - 1)
End FOOD_TYPE
FROM YOUR_TABLE) RS
GROUP BY RS.FOOD_TYPE

Upvotes: 1

Gabe
Gabe

Reputation: 462

create table MyTable
(id int, FieldName varchar(50) )

insert into MyTable values (1, 'Eggs')
insert into MyTable values (2, 'Bacon')
insert into MyTable values (3, 'Bacon Smoked')



select count(FieldName), FieldName from (
select 
    case
        when charindex('eggs', FieldName) > 0 then 'eggs'
        when charindex('bacon', FieldName) > 0 then 'bacon'
    end as FieldName
from MyTable) as myMyTablealias
group by FieldName

check it out

Upvotes: 0

Linger
Linger

Reputation: 15048

How about the following (Demo):

SELECT 'Eggs' AS Category, COUNT(*) AS MyCount
FROM MyTable
WHERE MyField LIKE '%Eggs%'
UNION ALL
SELECT 'Bacon' AS Category, COUNT(*) AS MyCount
FROM MyTable
WHERE MyField LIKE '%Bacon%'

Upvotes: 1

mxix
mxix

Reputation: 3659

This is a very specific case.. can you provide more Data? Does this help in anyway?

with list (item) as (
    select  
    item
    from (
        values 
            ('Eggs'),
            ('Bacon'),
            ('Bacon Smoked')) list (item)
)
select
    LEFT(item,
        (case 
            when CHARINDEX(' ',item,1) = 0 
            then LEN(item) 
            else CHARINDEX(' ',item,1) end)
    ) filtered,
    COUNT(*)
from list
group by
    LEFT(item,
    (case 
        when CHARINDEX(' ',item,1) = 0 
        then LEN(item) 
        else CHARINDEX(' ',item,1) end))

Upvotes: 0

Related Questions