PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Total and Sum of two columns in a single query

My requirement is similar to this question & answer PostgreSQL – Calculate SUM() of COUNT()
But need the total by another column

I have something similar to this

create table Item
(
     ItemID          INTEGER               not null,
     ItemCode        NCHAR(10)             not null,
     ItemName        NCHAR(10)             not null,
     constraint PK_ITEM primary key (ItemID)
);

create table Store
(
    StoreID             INTEGER             not null,
    StoreName           NCHAR(20)           not null,
    ItemID              INTEGER             not null,
    Location            NCHAR(20)           not null,
    constraint PK_STORE primary key (StoreID),
    foreign key (ItemID) references Item(ItemID)
);

insert into Item (ItemID,ItemCode,ItemName) Values (1,'abc','abc');
insert into Item (ItemID,ItemCode,ItemName) Values (2,'def','def');
insert into Item (ItemID,ItemCode,ItemName) Values (3,'ghi','ghi');

insert into Store (StoreID,StoreName,ItemID,Location) Values (1,'B1',1,'L1');
insert into Store (StoreID,StoreName,ItemID,Location) Values (2,'B2',2,'L2');
insert into Store (StoreID,StoreName,ItemID,Location) Values (3,'B3',1,'L3');
insert into Store (StoreID,StoreName,ItemID,Location) Values (4,'B4',2,'L1');
insert into Store (StoreID,StoreName,ItemID,Location) Values (5,'B5',3,'L2');
insert into Store (StoreID,StoreName,ItemID,Location) Values (6,'B6',2,'L3');
insert into Store (StoreID,StoreName,ItemID,Location) Values (7,'B7',3,'L1');
insert into Store (StoreID,StoreName,ItemID,Location) Values (8,'B8',1,'L3');
insert into Store (StoreID,StoreName,ItemID,Location) Values (9,'B9',2,'L1');

I have tried this

select count(I.ItemID), S.ItemID, I.ItemCode,  count(S.Location),S.Location
from Store S, Item I where S.ItemId=I.ItemID 
group by S.ItemID, I.ItemCode, S.Location ;

This results in this

count itemid itemcode count location
2 1 abc 2 L3
1 1 abc 1 L1
1 2 def 1 L3
2 2 def 2 L1
1 2 def 1 L2
1 3 ghi 1 L2
1 3 ghi 1 L1

I want something like this where the total displayed for itemId

count itemid itemcode count location
3 1 abc 2 L3
3 1 abc 1 L1
4 2 def 1 L3
4 2 def 2 L1
4 2 def 1 L2
2 3 ghi 1 L2
2 3 ghi 1 L1

or preferably like this

itemid itemcode count location
1 abc 2 L3
1 abc 1 L1
1 Total 3 <——-
2 def 1 L3
2 def 2 L1
2 def 1 L2
2 Total 4 <——-
3 ghi 1 L2
3 ghi 1 L1
3 Total 2 <——-

How to achieve this?

Tags:

2

Answers


  1. 0

    You can have separated selects in a single query and combine the result using join or union. For the first expected output, you can query like below

    select sq1.count, sq1.ItemID, sq2.ItemCode, sq2.count, sq2.Location from (
  select S1.ItemID, count(*)  as count
  from Store S1
  group by S1.ItemID
) sq1
inner join (
  select S2.ItemID, I.ItemCode, Count(*), S2.Location from Store S2 
  inner join Item I
  on S2.ItemId = I.ItemId
  group by S2.ItemID, I.ItemCode, S2.Location
) sq2
on sq1.ItemID = sq2.ItemID
order by sq2.ItemID, sq2.ItemCode;

    For the second result, you can use this query

    select * from (
  select S.ItemID, I.ItemCode, Count(*), S.Location from Store S 
  inner join Item I
  on S.ItemId = I.ItemId
  group by S.ItemID, I.ItemCode, S.Location
  union
  select  S2.ItemID, 'Total'  as itemcode, Count(*), null
  from Store S2
  group by S2.ItemID
) sq
order by sq.ItemID, sq.ItemCode;
    Login or Signup to reply.
  2. 0

    You can use the ROLLUP clause:

        select I.ItemID,  case when S.Location is null then 'Total' else I.ItemCode end as ItemCode, count(*) as count,  S.Location
    from #Store S
         inner join
         #Item I 
         on S.ItemId=I.ItemID 
    group by  I.ItemID,I.ItemCode, rollup(S.Location)
    order by I.ItemId, S.Location NULLS LAST

    But you have to make sure that the S.Location will not have nulls (because I used it to flag the subtotal row).

    Honestly, subtotals and grand totals etc are better handled by the presentation layer (reporting tool, or a form, etc..), because the SQL engines have limited facilities to deal with myriads of display options users might ask for.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search