1

This almost work but I want to fill the missing years with zeroes or null:

SELECT avg(salary), year, userId FROM salaries WHERE userId IN ('DICK', 'JANE') GROUP BY year, userId

Let me try to explain the background Let say that 'DICK' has salary between 2000 and 2005. 'JANE' has salary between 2003 and 2007. Between 2003 and 2005 I will have salary for both userIds which is good. But then I only have average salary for 'JANE' during 2006 and 2007. ...and I will only have average salary for 'DICK' during 2000 and 2002.

Here is what I want: For 'JANE' I want the average salary to be zero or null during her non-salary years 2000-2002. Likewise I want 'DICK'to have zero or null salary between 2006 and 2007.

Is this possible to add zeroes or null while running the select? Remember that I dont want to alter the table in my sql file. And I also want to do as much as possible in one select query. I just want an even select query answer because I need the answer for another square html table. Like it is now the table is not square. I will use "google.visualization.arrayToDataTable" and that must be square and I will have the axis by Year and by salary and the column names will be the UserIds.

#### This is the table I get with this query ####

Note: This is just an example of 2 amount of userIds. In the end it will be X amount of userIds.

SELECT avg(salary), year, userId FROM salaries WHERE userId IN ('DICK', 'JANE') GROUP BY year, userId

The sqlite answer i get when i run my query string

            ------My sqlite table----------
            | avg(salary) | year | userId |
            -------------------------------
            | 100         | 2000 | DICK |
            | 200         | 2001 | DICK |
            | 300         | 2002 | DICK |
            | 400         | 2002 | JANE |
            | 400         | 2003 | DICK |
            | 500         | 2003 | JANE |
            | 500         | 2004 | DICK |
            | 600         | 2004 | JANE |
            | 600         | 2005 | DICK |
            | 700         | 2005 | JANE |
            | 800         | 2006 | JANE |
            | 900         | 2007 | JANE |

#### This is what I want in the end ...how can I do this ####

            var data = google.visualization.arrayToDataTable([
              ['Year', 'JANE', 'DICK'],
              ['2000',  null,      100],
              ['2001',  null,      200],
              ['2002',  400,      300],
              ['2003',  500,      400],
              ['2004',  600,      500],
              ['2005',  700,      600],
              ['2006',  800,      null],
              ['2007',  900,      null]
            ]);

Please someone help me with this if it is possible

user2034859
  • 577
  • 1
  • 9
  • 20
  • You can do that in your UI. Fill all with 0s and only change those values where your cursor returns "non 0". It's all in your cursor. Just make your UI logic a little smarter. – Phantômaxx May 28 '14 at 18:22
  • Thanks for your time and thanks for helping me:) DICK and JANE is just an example of two userIds...it will actually be X amount of userIds...depending on what amount of userIds that are put in the UI box. So I really dont know how big the "google.visualization.arrayToDataTable" will be in the end. I will edit my question to try to explain more if I can:) – user2034859 May 29 '14 at 05:41
  • It sound like you know more than I can understand...sorry:) Why is my returned answer from my query string look like that then? – user2034859 May 29 '14 at 09:57
  • Your problem lies with the fact that you want to pivot the results as well as add zeroes. The zeroes themselves are relatively easy: just self-outer-join the salaries table with its `select distinct year from salaries` subset. That will give you values for each user for all years. The pivot then is harder to get. For hint see http://stackoverflow.com/a/1237082/1760858 – velis Jan 26 '15 at 11:43

0 Answers0