Select sum with input from grouped query-CodePudding

I need to make a query that selects a grouped collection of rows from a table based on user input conditions, and then in the select i will sum data from a subset of the rows.

The setup is rather expansive to describe in a post, so here is a demostration of the problem in the simplest way i can make it:

We have this table: DemoTable

ID	StaticKey	GroupKey	Value
1	A	A	2
2	A	A	2
3	A	B	2
4	A	B	2
5	A	C	2
6	A	C	2

I make a select and groups on "StaticKey". What i would then like to do, is to, in the select clause, to select the sum of a subset of the values from the groupped data:

select 
    DT.GroupKey,       
    (select sum(D.Value) from DemoTable D where D.ID in (DT.ID) and D.GroupKey = 'A') as 'Sum of A''s',
    (select COUNT(D.ID) from DemoTable D where D.ID in (DT.ID) and D.GroupKey = 'A')  as 'Count of A''s'
from DemoTable DT
group by DT.StaticKey;

I hoped that the sum would result in a sum of 4 and a count of 2, but i get 2 and 1. So the input to the "select sum" seems to be just one id and not the collected ids.

GroupKey	Sum of A's	Count of A's
A	2	1

If i add a group_concat of DT.ID i get them comma separated - but is it posible to get them as a collection i can use as input to the selects?

Heres sql to create the table and queries:

CREATE TABLE DemoTable
(
    ID        INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    GroupKey  varchar(200)     null     default null,
    StaticKey varchar(200)     not null default 'A',
    Value     varchar(200)     null     default null,
    PRIMARY KEY (ID)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

insert into DemoTable (GroupKey, Value) values ('A', 2);
insert into DemoTable (GroupKey, Value) values ('A', 2);
insert into DemoTable (GroupKey, Value) values ('B', 2);
insert into DemoTable (GroupKey, Value) values ('B', 2);
insert into DemoTable (GroupKey, Value) values ('C', 2);
insert into DemoTable (GroupKey, Value) values ('C', 2);


select DT.GroupKey,
       (select sum(D.Value) from DemoTable D where D.ID in (DT.ID) and D.GroupKey = 'A') as 'Sum of A''s',
       (select COUNT(D.ID) from DemoTable D where D.ID in (DT.ID) and D.GroupKey = 'A')  as 'Count of A''s'
from DemoTable DT
group by DT.StaticKey;

DROP TABLE DemoTable;

CodePudding user response：

More simple:

select GroupKey,
       sum(Value) as sum_of_A,
       sum(GroupKey='A') as count_of_A
from DemoTable
where GroupKey='A'  
group by  GroupKey;

https://dbfiddle.uk/sdYlTw57

CodePudding user response：

Isn't it always D.ID in (DT.ID)?

select 
    DT.GroupKey,       
    (select sum(D.Value) from DemoTable D where D.GroupKey = 'A') as 'Sum of A''s',
    (select COUNT(D.ID) from DemoTable D where D.GroupKey = 'A')  as 'Count of A''s'
from DemoTable DT
group by DT.StaticKey;

It does the job but perhaps it's too simple...

CodePudding user response：

You can also try this.

SELECT DT.GroupKey,
SUM(DT.Value) AS 'Sum of A''s',
COUNT(DT.ID) AS 'Count of A''s'
FROM DemoTable DT
WHERE DT.GroupKey = 'A'
GROUP BY DT.StaticKey;