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

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

CodePudding user response：

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

CodePudding user response：

total number of locations (count) the item is associated with for a given branch

The way you described it, you should

remove location from query:

SQL> with branchinventoryfile (branch, item, location) as
  2    (select 100, 'A', 1111 from dual union all
  3     select 100, 'A', 1112 from dual union all
  4     select 200, 'A', 2111 from dual
  5    )
  6  select branch,
  7         item,
  8         count(distinct location) cnt
  9  from BranchInventoryFile
 10  group by branch, item;

    BRANCH I        CNT
---------- - ----------
       100 A          2
       200 A          1

SQL>

if you leave location in select, you have to group by it (and get wrong result):

  6  select branch,
  7         item,
  8         location,
  9         count(distinct location) cnt
 10  from BranchInventoryFile
 11  group by branch, item, location;

    BRANCH I   LOCATION        CNT
---------- - ---------- ----------
       100 A       1111          1
       200 A       2111          1
       100 A       1112          1

SQL>

or include locations, but aggregate them, e.g.

  6  select branch,
  7         item,
  8         listagg(location, ', ') within group (order by null) loc,
  9         count(distinct location) cnt
 10  from BranchInventoryFile
 11  group by branch, item;

    BRANCH I LOC                         CNT
---------- - -------------------- ----------
       100 A 1111, 1112                    2
       200 A 2111                          1

SQL>