Count of distinct values

Question

I've read many posts here about counting distinct values, but couldn't find the right solution for me. At work I created a Openoffice spreadsheet connected to a database, which looks like this:

packID Date Surname Name Substance A Substance B Substance C 1 5.9.2015 Doe John 50 50 100 2 5.9.2015 Parker Peter 25 30 60 3 5.9.2015 Doe John 15 80 20 4 5.9.2015 Doe John 40 20 140 5 5.9.2015 Johnson Jack 80 50 10 6 5.9.2015 Black James 15 35 20

This is a one day example, I'd like to have an outcome looking like this:

Surname Name TotalVolume Count Black James 70 1 Doe John 200 2 Doe John 115 1 Johnson Jack 140 1 Parker Peter 115 1

Total volume consist of three substances (A+B+C). I'd be happy for every advice. Thanks in advance.

Please explain the "count" column and tag your question with the correct database. — Gordon Linoff
– Gordon Linoff, Commented Sep 5, 2015 at 18:30

Lukasz Szozda · Accepted Answer · 2015-09-05 18:48:33Z

Calculate TotalVolume in subquery and then just group by using Surname, Name and TotalVolume.

SqlFiddleDemo

SELECT Surname, Name, TotalVolume, COUNT(*) AS Count FROM ( SELECT Surname, Name, Substance_A + Substance_B + Substance_C AS TotalVolume FROM tab) AS t GROUP BY Surname, Name, TotalVolume

Or without subquery:

SELECT Surname, Name, Substance_A + Substance_B + Substance_C AS TotalVolume, COUNT(*) AS Count FROM tab GROUP BY Surname, Name, Substance_A + Substance_B + Substance_C

Collectives™ on Stack Overflow

Count of distinct values

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related