sql server - SQL count and group items by two columns -

this select have:

select s.productid, s.fromsscc, l.receiptid  logstock s left join log l on l.id = s.logid l.receiptid=1760 

with following results:

|productid |sscc   |receiptid |363       |22849  |1760 |364       |22849  |1760 |1468      |22849  |1760 |1837      |22849  |1760 |384       |22849  |1760 |390       |22849  |1760 |370       |22849  |1760 |391       |22849  |1760 |371       |21557  |1760 |391       |21556  |1760 |390       |21555  |1760 |370       |21554  |1760 |389       |21553  |1760 

i need transform select outcome:

|palet type1    |palet type2 |1              |5 

the logic is:

• if single sscc (22849 in example) has more 1 productid, type 1
• if single sscc (21557,21556,21555,21554,21553 in example) has 1 productid type 2

how count how many ssccs each type have (on basis of productids)?

you have group , count. can use common table expression simplify query.

with types (sscc, type) (   select s.sscc,     case when count(s.productid) > 1 1 else 2 end type   stock s   s.receiptid = 1760   group s.sscc ) select   (select count(*) types type = 1) type_1,   (select count(*) types type = 2) type_2 

sql fiddle : http://sqlfiddle.com/#!3/85cea/5