SQL: How to select multiple COUNT values that share NAME field?

93
March 25, 2019, at 02:50 AM

I have an APACHE DERBY database table named STUDENTS with the following rows:

_____________________________
| NAME  |COUNTER|   MONTH   |
-----------------------------
| bill  |   10  |   January |
-----------------------------
| bill  |   12  |   February|
-----------------------------
| bill  |   11  |   March   |
-----------------------------
| sam   |   14  |   January |
-----------------------------
| sam   |   9   |   February|
-----------------------------
| sam   |   22  |   March   |
-----------------------------
| hal   |   21  |   January |
-----------------------------
| hal   |   18  |   February|
-----------------------------
| hal   |   19  |   March   |
-----------------------------

I am trying to find the syntax to get the following output...

_________________________________
| NAME  |  JAN  |  FEB  |   MAR |
---------------------------------
| bill  |  10   |  12   |  11   |
---------------------------------
| sam   |  14   |   9   |  22   |
---------------------------------
| hal   |  21   |  18   |  19   |
---------------------------------

I tried the following:

select distinct(name), 
    (SELECT COUNTER FROM STUDENTS WHERE NAME = 'bill' AND month = 'january') as jan,
    (SELECT COUNTER FROM STUDENTS WHERE NAME = 'bill' AND month = 'february') as feb,
    (SELECT COUNTER FROM STUDENTS WHERE NAME = 'bill' AND month = 'march') as mar
    FROM STUDENT where name = 'bill'

...and it kind of works as a proof of concept for one student. However, it's wordy, it only works for one student, and it does not scale for any data from future months that I will add to the db. Meaning, I would like to be able to get just the last 3 COUNTER values for each NAME regardless of how many months of data exits in the db.

I have looked at multiple s.o. questions related to nested queries, but none of them helped.

Suggestions?

Any help is appreciated!

Answer 1

You can use conditional aggregation

SELECT name,
       Max(CASE WHEN month = 'January'  THEN counter end) AS jan,
       Max(CASE WHEN month = 'February' THEN counter end) AS feb,
       Max(CASE WHEN month = 'March'    THEN counter end) AS mar
  FROM student
 GROUP BY name  
READ ALSO
Sequelize.js table - unable to export as SQL from PHPMyAdmin

Sequelize.js table - unable to export as SQL from PHPMyAdmin

I am using Sequelizejs as my ORM for MariaDB 10

55
Generate Column representing the row's index within the group

Generate Column representing the row's index within the group

I have a case where I have a table, which describes measurements at a certain dateHowever, there are different types of measurements (which may have the same date)

77
Why are these two seemingly-similar MySQL queries returning radically different results?

Why are these two seemingly-similar MySQL queries returning radically different results?

I want to pull a list of units that have an overall rating of less than 4The first query returns 1760 rows with data that seems to check out when cross referenced

78
Is it better to create a table when you have to use a derived column in many queries?

Is it better to create a table when you have to use a derived column in many queries?

I have some queries to do and I'm ending up using too many subqueriesOne of the subqueries is to find a derived value which comes from summing two columns

47