• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

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.
0
Col-Z
Asked:
Col-Z
  • 3
  • 2
1 Solution
 
momi_sabagCommented:
which database are you using?
if you are using sql server, you can use the pivot clause
0
 
Col-ZAuthor Commented:
I am using MS Access 2007
0
 
momi_sabagCommented:
do you have a limited number of items?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Col-ZAuthor Commented:
Yes, there is a limited number of items.
0
 
karunamoorthyCommented:
You can try this and give your remakrs pl.

TRANSFORM ItemSold
SELECT EmployeeID
FROM SalesHistoy
GROUP BY EmployeeID
PIVOT ItemSold
0
 
Col-ZAuthor Commented:
Thanks!

That was exactly what I was after!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now