Case statement

Question

Thanks in advance!

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

Answer 1

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

Answer 2

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
*/

Answer 3

Edit the original post, could you please have another look, thanks.

Answer 4

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

- Krishnakumar S

Answer 5

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

Answer 6

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.

Answer 7

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

Answer 8

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

Answer 9

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