CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Gopal
Gopal

Reputation: 11982

Getting a count of each product for each ID

Table1

ID product

001 LG
001 Sony
001 LG
001 LG
001 Sony
001 BPL
001 BPL
001 Samsung
002 LG
002 BPL
002 LG
...,

I want to take each product count for each personid.

Expected Output

ID BPL LG Samsung Sony

001 2 3 1 2
002 1 1 0 0
..,

How to make a query of taking each product count for each personid.

Need SQL Query Help.

Upvotes: 0

Views: 721

Answers (3)

masoud ramezani
masoud ramezani

Reputation: 22940

Use PIVOT command for this:

look at this example:

SELECT s.Name ShiftName,

h.BusinessEntityID,

d.Name DepartmentName

FROM HumanResources.EmployeeDepartmentHistory h

INNER JOIN HumanResources.Department d ON

h.DepartmentID = d.DepartmentID

INNER JOIN HumanResources.Shift s ON

h.ShiftID = s.ShiftID

WHERE EndDate IS NULL AND

d.Name IN ('Production', 'Engineering', 'Marketing')

ORDER BY ShiftName

the return table of this query:

ShiftName   BusinessEntityID   DepartmentName
Day              3                            Engineering
Day              9                            Engineering
...
Day              2                            Marketing
Day              6                            Marketing
...
Evening          25                          Production
Evening          18                          Production
Night            14                          Production
Night            27                          Production
...
Night            252                        Production
(194 row(s) affected)

now you can change the format of returned table :

SELECT ShiftName,

Production,

Engineering,

Marketing

FROM

(SELECT s.Name ShiftName,

h.BusinessEntityID,

d.Name DepartmentName

FROM HumanResources.EmployeeDepartmentHistory h

INNER JOIN HumanResources.Department d ON

h.DepartmentID = d.DepartmentID

INNER JOIN HumanResources.Shift s ON

h.ShiftID = s.ShiftID

WHERE EndDate IS NULL AND

d.Name IN ('Production', 'Engineering', 'Marketing')) AS a

PIVOT

(

COUNT(BusinessEntityID)

FOR DepartmentName IN ([Production], [Engineering], [Marketing])

) AS b

ORDER BY ShiftName

the returned table is like below:

ShiftName   Production   Engineering   Marketing
Day              79                 6                    9
Evening          54                 0                    0
Night            46                 0                    0

(3 row(s) affected)

Upvotes: 2

MtnViewMark
MtnViewMark

Reputation: 5140

You can't in general because the number of columns in the output would depend on the number of distinct value stored in the product column. If you know all the possible products in advance, you can hard code them into the query as in @Thomas' answer. Otherwise, the best you can do is:

Select ID, product, count(*) as cnt
From Table1
Group By ID, product

Which will yield a table like:

ID  product cnt

001 LG      3
001 Sony    2
001 BPL     1
001 Samsung 1
002 LG      2
002 BPL     1

Upvotes: 0

Thomas
Thomas

Reputation: 64655

Select ID
        , Sum( Case When Product = 'BPL' Then 1 Else 0 End ) As BPL
        , Sum( Case When Product = 'Sony' Then 1 Else 0 End ) As Sony
        , Sum( Case When Product = 'LG' Then 1 Else 0 End ) As LG
        , Sum( Case When Product = 'Samsung' Then 1 Else 0 End ) As Samsung
From Table
Group By ID

This is a classic crosstab problem. You can google for many similar answers.

Upvotes: 3

Related Questions