Solved

Multiple Counts in one query

Posted on 2012-04-13
7
219 Views
Last Modified: 2012-04-13
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
Comment
Question by:Col-Z
  • 3
  • 2
7 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37843330
which database are you using?
if you are using sql server, you can use the pivot clause
0
 

Author Comment

by:Col-Z
ID: 37843419
I am using MS Access 2007
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37843438
do you have a limited number of items?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:Col-Z
ID: 37843472
Yes, there is a limited number of items.
0
 
LVL 7

Accepted Solution

by:
karunamoorthy earned 345 total points
ID: 37843599
You can try this and give your remakrs pl.

TRANSFORM ItemSold
SELECT EmployeeID
FROM SalesHistoy
GROUP BY EmployeeID
PIVOT ItemSold
0
 

Author Closing Comment

by:Col-Z
ID: 37843640
Thanks!

That was exactly what I was after!
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question