Link to home
Start Free TrialLog in
Avatar of Col-Z
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.ItemSold) 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.
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

which database are you using?
if you are using sql server, you can use the pivot clause
Avatar of Col-Z
Col-Z

ASKER

I am using MS Access 2007
do you have a limited number of items?
Avatar of Col-Z

ASKER

Yes, there is a limited number of items.
ASKER CERTIFIED SOLUTION
Avatar of karunamoorthy
karunamoorthy
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Col-Z

ASKER

Thanks!

That was exactly what I was after!