Col-Z
asked on
Multiple Counts in one query
I am relatively new to SQL and would like some help on how to do multiple counts in one query.
An example would be:
table: Sales History
EmployeeID ItemSold
0 1
2 2
2 1
0 2
1 1
2 3
2 2
0 2
2 1
1 1
0 3
1 2
1 3
2 3
How do I write a query to return how many of each item each employee sold summary?
EmployeeID Item1 Item2 Item2
0 1 2 1
1 2 1 1
2 2 2 2
I can get it to count one Item, with the following select statement,
SELECT SalesHistoy.EmpID, Count(SalesHistory.ItemSol d) as Item1 From SalesHistoy WHERE SalesHistoy.ItemSold=1 GROUP BY Sales.Histoy.EmpID
but have struggled to get it to work for more items without using multiple queries.
Any advice would be greatly appreciated.
An example would be:
table: Sales History
EmployeeID ItemSold
0 1
2 2
2 1
0 2
1 1
2 3
2 2
0 2
2 1
1 1
0 3
1 2
1 3
2 3
How do I write a query to return how many of each item each employee sold summary?
EmployeeID Item1 Item2 Item2
0 1 2 1
1 2 1 1
2 2 2 2
I can get it to count one Item, with the following select statement,
SELECT SalesHistoy.EmpID, Count(SalesHistory.ItemSol
but have struggled to get it to work for more items without using multiple queries.
Any advice would be greatly appreciated.
ASKER
I am using MS Access 2007
do you have a limited number of items?
ASKER
Yes, there is a limited number of items.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
That was exactly what I was after!
That was exactly what I was after!
if you are using sql server, you can use the pivot clause