Solved

Multiple Counts in one query

Posted on 2012-04-13
7
218 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

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

776 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