Question 21584 in Mysql Question posted in
The official MySQL documentation can be found here.

Select sum with input from grouped query – Mysql

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;
Tags:

3

Answers


  1. 0

    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…

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
  3. 0

    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

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

Back To Top
Search