Total and Sum of two columns in a single query

Question

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?

add this as column in your query ,sum(cnt) over (partition by s.itemid )tot_cnt — PIG
– PIG, Commented Sep 8, 2024 at 14:30

tinazmu · Accepted Answer · 2024-09-06 04:14:16Z

3

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.

answered Sep 6, 2024 at 4:14

tinazmu

5,5442 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonas Metzler Over a year ago

This is clever! It seems all those columns are never NULL according to the table definition.

vht981230 · Accepted Answer · 2024-09-06 03:14:53Z

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;

JohnH · Accepted Answer · 2024-09-06 04:50:57Z

1

The following query uses GROUPING SETS to achieve the OP's preferred results:

SELECT
  s.itemid,
  COALESCE(i.itemcode, 'Total') AS itemcode,
  COUNT(i.itemid),
  COUNT(s.location),
  s.location
FROM
  store s
  JOIN item i ON s.itemid = i.itemid
GROUP BY
  GROUPING sets ((s.itemid, i.itemcode, s.location), (s.itemid))
ORDER BY
  s.itemid,
  s.location;

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

answered Sep 6, 2024 at 4:50

JohnH

3,2932 gold badges6 silver badges20 bronze badges

Collectives™ on Stack Overflow

Total and Sum of two columns in a single query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related