Case statement

# Case statement

• Wednesday, May 09, 2012 6:34 PM

Table structure:

col1: the sequence number is the pk, auto number

col2: the location name,

col3,4 : some other data, not related to the query

col5: the product name used in a certain location.

Requirement: need to count( each location's product number ).

Need the query result:

Loc    Product    number

a           F             2

b           F             1

b           O             1

c           O            3

from the following sample data. many thanks.

1, a, XX, c,  F

2, a, XX, aa, F

3, b, YY,bb, F

4, b, YY, cc, O

5, c, ZZ, dd, O

6, c, ZZ,ll,  O

7,c. ZZ, ss,  O

### All Replies

• Wednesday, May 09, 2012 6:39 PM

Hello,

Can you please explain the logic for the result; just by the sample data + result I don't get it.

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

• Wednesday, May 09, 2012 6:40 PM
Moderator

This is weak on the description; I will go ahead and give an empiracal answer, but without a set of rules to describe the process, the potential query might be dicey; hang on.

A test table:

```declare @test table
( an_Id integer,
loc char(1),
col3 varchar(2),
col4 varchar(2),
Product char(1) );
insert into @test
select 1, 'a', 'b',  'c',  'F' union all
select 2, 'a', 'x',  'aa', 'F' union all
select 3, 'b', 'aa', 'bb', 'F' union all
select 4, 'b', 'af', 'cc', 'O' union all
select 5, 'c', 'aa', 'dd', 'O' union all
select 6, 'c', 'ee', 'll', 'O' union all
select 7, 'c', 'e',  'ss', 'O';```

Perhaps something like:

```;
with cte as
( select
loc,
product,
min(product) over(partition by loc
) as min_Product,
count(product) num
from @test
group by loc, product
)
select
loc,
product,
num
from cte
where min_Product = product
/* -------- Output: --------
loc  product num
---- ------- -----------
a    F       2
b    F       1
c    O       3
*/```

EDIT:

Based on the modified requirements:

```select
loc,
product,
count(product) num
from @test
group by loc, product
/* -------- Output: --------
loc  product num
---- ------- -----------
a    F       2
b    F       1
b    O       1
c    O       3
*/```

• Wednesday, May 09, 2012 6:53 PM

Edit the original post, could you please have another look, thanks.
• Wednesday, May 09, 2012 6:58 PM

`SELECT Loc, Product, Number = COUNT(*) FROM YourTable GROUP BY Loc, Product`

- Krishnakumar S
• Wednesday, May 09, 2012 7:01 PM
Moderator

select Loc, Product, count(Product) as [Product Count]

from myTable

GROUP BY Loc, Product

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Wednesday, May 09, 2012 7:05 PM
Moderator

Edit the original post, could you please have another look, thanks.
I edited my previous response to reflect the change; it is pretty much the same answer as posted by Krishna and Naomi.
• Wednesday, May 09, 2012 7:47 PM

Thanks so much for all your great help! This works, and I need another result set as below ( if not possible in one query to get the all data, then it will be ok to remove the total in the result set):

Need the query result:

Loc    Product   Total    XX       YY      ZZ

a           F          2        2

b           F         1         1

b           O        1                     1

c           O        3                              3

from the following sample data. many thanks.

1, a, XX, c,  F

2, a, XX, aa, F

3, b, YY,bb, F

4, b, YY, cc, O

5, c, ZZ, dd, O

6, c, ZZ,ll,  O

7,c. ZZ, ss,  O

• Wednesday, May 09, 2012 7:59 PM
Moderator

Try adding the PIVOT operator to Naomi's or Krishna's query

• Wednesday, May 09, 2012 8:36 PM
Moderator

If XX, YY, ZZ are predefined, then

```select Loc, Product, count(*) as Total,

count(case when Col2 = 'XX' then Product end) as XX,

count(case when Col2 = 'YY' then Product end) as YY,

count(case when Col2 = 'ZZ' then Product end) as ZZ

from ProductLocations

GROUP BY Loc, Product```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog