SQL: Get SUM of grouped query having count statement

Question

I'm looking for a query that counts all entries which have more than 1 entry. e.g.:

   adrnr   value   datum
   15      8.68    2021-08-29
   10      16.4    2021-08-30
   15      33.6    2021-08-30
   16      125.98  2021-08-31
   10      23.44   2021-08-31
   18      19.87   2021-08-31
   16      26.87   2021-09-01
   10      10.0    2021-09-01

I came up with following query:

SELECT adrnr, COUNT(adrnr) as summe FROM tablename WHERE datum >= '2021-01-08 00:00:00.000' 
AND datum <= '2021-31-08 23:59:59.999' group by adrnr having count(adrnr) > 1

I then get an answer:

   adrnr  summe
   15     2
   10     2

This is fine, but I want to get just one SUM of all entries, i.e.:

summe
4

How do I get this answer. And after that I want to get the the SUM of all values that depends on the same condition, i.e. 82.12 Any idea?

Regards Jens

Tim Biegeleisen · Accepted Answer · 2021-09-14 09:04:22Z

2

Use a subquery against your current query:

SELECT SUM(summe)
FROM
(
    SELECT COUNT(adrnr) AS summe
    FROM tablename
    WHERE datum >= '2021-08-01' AND datum < '2021-09-01'
    GROUP BY adrnr
    HAVING COUNT(drnr) > 1
) t;

Note: Your timestamp/date literals looked a bit off. The above assumes you only want to target the month of August, 2021.

answered Sep 14, 2021 at 9:04

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

Jens Körte Over a year ago

woah. This was a fast answer. Thanks a lot. Didn't saw the solution by my own. Yes, my date literals could be better.

Collectives™ on Stack Overflow

SQL: Get SUM of grouped query having count statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related