Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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