SQL help to count number of locations for each item/branch

Question

I'm a SQL rookie, and am having trouble wrapping my head around how to do the following. I have a table that contains item information by branch. Within a branch an item can be in multiple locations. The data I need to extract needs to include a column that provides the total number of locations (count) the item is associated with for a given branch.

Output would look something like this:

I'm guessing this is a sub query, but to be honest I'm not sure how to get started... order in which this is done (subquery group by first, then join, etc)

In purely logical terms:

SELECT
a.Branch,
a.Item,
a.Loc,
COUNT(a.Branch||a.Item) AS 'LocCount' 
FROM BranchInventoryFile a
GROUP BY a.Branch,a.Item

Answer 1

You can tackle this by using Oracle's Count Analytical functions found here. Be sure to read up on WINDOW/Partitioning functions as this unlocks quite a bit of functionality in SQL.

SQL:

SELECT 
  a.BRANCH, 
  a.ITEM, 
  a.LOC, 
  COUNT(a.ITEM) OVER (PARTITION BY a.BRANCH, a.ITEM) AS LOC_COUNT 
FROM 
  BRANCH a;

Result:

| BRANCH | ITEM |  LOC | LOC_COUNT |
|--------|------|------|-----------|
|    100 |    A | 1111 |         2 |
|    100 |    A | 1112 |         2 |
|    200 |    A | 2111 |         1 |
|    200 |    B | 1212 |         2 |
|    200 |    B | 1212 |         2 |
|    300 |    A | 1222 |         1 |

SQL Fiddle: Here