1

Newbie question. I have this table A:

theDate     |  concept    |  datum
DATE        |  VARCHAR    |  FLOAT
----------------------------------
2000-01-01  |  Concept A  |  19
2000-01-01  |  Concept B  |  10
2000-01-01  |  Concept C  |  0
2001-01-01  |  Concept A  |  30
2001-01-01  |  Concept B  |  15
2001-01-01  |  Concept C  |  0

I need to calculate the percentages:

theDate     |  concept    |  percentage
DATE        |  VARCHAR    |  FLOAT
----------------------------------
2000-01-01  |  Concept A  |  65.51724138
2000-01-01  |  Concept B  |  34.48275862
2000-01-01  |  Concept C  |  0
2001-01-01  |  Concept A  |  66.66666667
2001-01-01  |  Concept B  |  33.33333333
2001-01-01  |  Concept C  |  0

Is it safe to use a single GROUP BY, without a subquery or a self JOIN? Like this:

SELECT theDate, concept, datum * 100 / sum(datum) percentage
FROM A
GROUP BY theDate

Note: I've reviewed this question Calculate percentage in SQL but I feel it doesn't answer mine.

Community
  • 1
  • 1
ArturoTena
  • 713
  • 5
  • 15
  • 2
    I presume you are using MySQL? Most other RDBMS would report an error here, and require you to include `concept, (datum * 100)` in the `GROUP BY` too. – Michael Berkowski Jun 30 '15 at 19:29
  • @MichaelBerkowski Not my RDBMS. It gives the correct result in this toy example, but I'm afraid to use it on production with Real Data :-) – ArturoTena Jun 30 '15 at 19:32
  • 1
    Working with only one table, and no join, _and_ non-varying values of `concept` it will be okay without a full `GROUP BY`. But if the value of `concept` changes within the `theDate` group, the RDBMS may report inconsistent and indeterminate results for that column. Please tag the question with the appropriate RDBMS – Michael Berkowski Jun 30 '15 at 19:36
  • @MichaelBerkowski "Full group by" is the name I was looking for in order by google :-) I've found this related with MySQL: http://www.tocker.ca/2014/01/24/proposal-to-enable-sql-mode-only-full-group-by-by-default.html It seems it's not standard SQL. I might want not to use then in Production. – ArturoTena Jun 30 '15 at 19:40
  • 1
    MySQL has a variable `ONLY_FULL_GROUP_BY` which defaults to false, to disallow this behavior. It is a habit I never permit myself to get into, but is nevertheless common among MySQL users. We often see confusion here on Stack Overflow when users switch from MySQL to something else and find they misuse `GROUP BY`. – Michael Berkowski Jun 30 '15 at 19:42
  • @MichaelBerkowski Thank you. Your comments have helped me to find the answer: it's supported by a few RDBMS but it seems not to be the standard. For future references, see [this](http://ocelot.ca/blog/blog/2014/01/26/mysql-group-by-select-lists-and-standard-sql/) and [this](http://rpbouman.blogspot.mx/2007/05/debunking-group-by-myths.html). Please, answer this question to accept it. Thank you! – ArturoTena Jun 30 '15 at 19:47
  • 1
    I upvoted your question - Rather than answer, I'm going to link it [against this one](http://stackoverflow.com/questions/22177767/why-does-mysql-allow-you-to-group-by-columns-that-are-not-selected/22178088#22178088) which already has 2 good answers more thorough than my comments here. – Michael Berkowski Jun 30 '15 at 19:51

1 Answers1

1

correct way is this .. but in mysql it wont give any error... 

SELECT theDate, concept, datum * 100 / sum(datum) percentage
FROM A
GROUP BY (theDate,concept)
kavetiraviteja
  • 2,058
  • 1
  • 15
  • 35