SQL Group By – How to Use Group By on Subquery Rows

group byMySQLsql serversubquery

I have this table :
ArretProductionJournee(Id, DateArret,HeureDebut,HeureFin,EnumArret)
Example :

DateArret   ||HeureDebut                 ||HeureFin                 ||EnumArret
2020-11-30  ||2020-11-30 14:00:00.000    ||2020-11-30 15:00:00.000  ||PS
2020-11-30  ||2020-11-30 16:00:00.000    ||2020-11-30 17:00:00.000  ||HI

i want to sum the datediff(HeureDebut,HeureFin) in columns for each EnumArret
so i run this query :

SELECT ArretProductionJournee.DateArret, 
(select
sum (datediff(minute,ArretProductionJournee.HeureDebut, 
ArretProductionJournee.HeureFin))
where ArretProductionJournee.EnumArret Like 'HI')as HI,

(select
sum (datediff(minute,ArretProductionJournee.HeureDebut, 
ArretProductionJournee.HeureFin))
where ArretProductionJournee.EnumArret Like 'PS') as PS

FROM            dbo.ArretProductionJournee
                where ArretProductionJournee.EnumArret Like 'HI'OR 
                ArretProductionJournee.EnumArret Like 'PS'
group by  ArretProductionJournee.EnumArret, dbo.ArretProductionJournee.DateArret

Result :

DateArret   ||HI   ||   PS
2020-10-30  ||12   ||   NULL
2020-11-30  ||60   ||   NULL
2020-11-30  ||NULL ||   60

The result i want is Grouping the sum by the date and the enumArret:

DateArret   ||HI   ||   PS
2020-10-30  ||12   ||   0
2020-11-30  ||60   ||   60

Best Answer

Rather than subqueries, use conditional aggregation:

SELECT
      DateArret
    , SUM(CASE EnumArret WHEN 'HI' THEN DATEDIFF(minute,HeureDebut, HeureFin) ELSE 0 END) AS HI
    , SUM(CASE EnumArret WHEN 'PS' THEN DATEDIFF(minute,HeureDebut, HeureFin) ELSE 0 END) AS PS
FROM dbo.ArretProductionJournee
WHERE EnumArret IN('HI', 'PS')
GROUP BY
    DateArret
ORDER BY
    DateArret;

Below is more complete sample data and schema to match your desired result.

CREATE TABLE dbo.ArretProductionJournee(
     ID int NOT NULL IDENTITY CONSTRAINT PK_ArretProductionJournee PRIMARY KEY
    ,DateArret date NOT NULL
    ,HeureDebut datetime2(3) NOT NULL
    ,HeureFin datetime2(3) NOT NULL
    ,EnumArret char(2) NOT NULL
);
INSERT INTO dbo.ArretProductionJournee(DateArret, HeureDebut, HeureFin, EnumArret)
VALUES
     ('2020-10-30', '2020-10-30 15:00:00.000', '2020-10-30 15:05:00.000', 'HI')
    ,('2020-10-30', '2020-10-30 15:00:00.000', '2020-10-30 15:07:00.000', 'HI')
    ,('2020-11-30', '2020-11-30 14:00:00.000', '2020-11-30 15:00:00.000', 'PS')
    ,('2020-11-30', '2020-11-30 16:00:00.000', '2020-11-30 17:00:00.000', 'HI')
    ,('2020-11-30', '2020-11-30 16:00:00.000', '2020-11-30 17:00:00.000', 'XX');

Related Solutions

MySQL sum of column group by question

SELECT id, name, SUM(result)
FROM (SELECT cat_id as id, name, result  FROM test
UNION ALL
SELECT t1.parent_id as id, t2.name, t1.result 
FROM test as t1 
LEFT JOIN test as t2 ON t2.cat_id = t1.parent_id
) as a 
GROUP BY id,name ORDER BY id

Mysql – How to get the SUM between dates in a WHERE clause

You might laugh when you here this, by there is an aggregate clause that triggers summary between breaks in values. The aggregrate clause is WITH ROLLUP. (Look in MySQL Documentation under GROUP BY Modifiers)

let's take your query and make the following changes

Remove ORDER BY
Substitute WITH ROLLUP
Remove name

You get this

SELECT
cdr.datefield AS 'date',
a.id AS 'id',
SUM(t.debit_amount - t.credit_amount) AS 'balance'
FROM calendar cdr
JOIN transactions t ON (cdr.datefield >= t.value_date)
JOIN accounts a ON (a.id = t.account_id)
WHERE cdr.datefield IN ('2014-03-31', '2013-03-31', '2012-03-31')
GROUP BY cdr.datefield, a.id
WITH ROLLUP;

Here is what should happen

In between each datefield, there will be
- datefield
- id NULL
- a sum of balance for that date
The last row return
- datefield NULL
- id NULL
- a sum of balance for all dates

Based on the desired output, you want Cash, Payables and Issued Capital as zero in case there is no transaction type for the given date. You also done want the last row because you don't want the grand total. What you do is take the new query, make it a subquery, and LEFT JOIN the accounts table to the subquery, returning zero for balance if it is NULL and exclude any row whose date is NULL.

Here is the new query

SELECT
    BB.date,AA.id,AA.name,IFNULL(BB.balance,0) balance
FROM accounts AA LEFT JOIN
(SELECT
cdr.datefield AS 'date',
a.id AS 'id',
SUM(t.debit_amount - t.credit_amount) AS 'balance'
FROM calendar cdr
JOIN transactions t ON (cdr.datefield >= t.value_date)
JOIN accounts a ON (a.id = t.account_id)
WHERE cdr.datefield IN ('2014-03-31', '2013-03-31', '2012-03-31')
GROUP BY cdr.datefield, a.id
WITH ROLLUP) BB USING (id) WHERE AA.date IS NOT NULL
ORDER BY BB.date DESC,AA.id;

I have used WITH ROLLUP to answer many posts in the DBA StackExchange

Jun 20, 2014 : Get only overall summary WITH ROLLUP and GROUP BY for multiple fields
Aug 12, 2014 : Fetch data from same table using two group by clauses in mysql
Apr 11, 2014 : Why is MySQL not using the index with the higher cardinality?
Sep 21, 2014 : Monthly report by time
Jul 31, 2013 : TokuDB database size unknown in phpmyadmin
Jul 10, 2013 : How to estimate/predict data size and index size of a table in MySQL
Jul 03, 2013 : Information about Disk Storage MySQL
Apr 25, 2013 : WITH ROLLUP WHERE x IS NULL

Give it a Try !!!

Best Answer

Related Solutions

MySQL sum of column group by question

Mysql – How to get the SUM between dates in a WHERE clause

Related Question