Google chart not picking up and displaying query

Question

I am new to using Google Charts and I cannot figure out why my query is not displaying in my Google Chart (Table). The Table does however display another query. The Query that I am trying to display doesn't bring up a table like my first query does.

I cannot seem to understand where the problem is, because I tested my query several times and even ran it in SQL Server, and it does generate the output that I am looking for, but it simply doesn't display in the Table

Working Query:

SELECT Researcher.FirstName + ' ' + Researcher.Surname AS [Researcher_Name], Count(Submission.SubmissionID) AS [Number_of_Submissions] FROM Submission INNER JOIN ((Faculty INNER JOIN School ON Faculty.FacultyID = School.[FacultyID]) INNER JOIN (Researcher INNER JOIN ResearcherSubmission ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID) ON School.SchoolID = Researcher.SchoolID) ON Submission.SubmissionID = ResearcherSubmission.SubmissionID GROUP BY Researcher.FirstName, Researcher.Surname;

This is the query that doesn't display the Google chart. Note: when I run this query in my SQL generator however, it does display the results I am looking for:

SELECT Submission.Title AS [Title], Submission.Status AS [Submission_Status] FROM Submission INNER JOIN ((Faculty INNER JOIN School ON Faculty.FacultyID = School.[FacultyID]) INNER JOIN (Researcher INNER JOIN ResearcherSubmission ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID) ON School.SchoolID = Researcher.SchoolID) ON Submission.SubmissionID = ResearcherSubmission.SubmissionID GROUP BY Submission.Status, Submission.Title;

This is my entire code with the working query and I have commented out the non-working one:

@{
                        var db = Database.Open("RMSContext");

                        String rows = "";



                        var Query = ("SELECT Researcher.FirstName + ' ' + Researcher.Surname AS [Researcher_Name], Count(Submission.SubmissionID) AS [Number_of_Submissions] FROM Submission INNER JOIN ((Faculty INNER JOIN School ON Faculty.FacultyID = School.[FacultyID]) INNER JOIN (Researcher INNER JOIN ResearcherSubmission ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID) ON School.SchoolID = Researcher.SchoolID) ON Submission.SubmissionID = ResearcherSubmission.SubmissionID GROUP BY Researcher.FirstName, Researcher.Surname;");

                        //var Query = ("SELECT Submission.Title AS [Title], Submission.Status AS [Submission_Status] FROM Submission INNER JOIN ((Faculty INNER JOIN School ON Faculty.FacultyID = School.[FacultyID]) INNER JOIN (Researcher INNER JOIN ResearcherSubmission ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID) ON School.SchoolID = Researcher.SchoolID) ON Submission.SubmissionID = ResearcherSubmission.SubmissionID GROUP BY Submission.Status, Submission.Title;");


                        var AppQuery = db.Query(Query);


                        List<string> rowsList = new List<string>();

                        foreach (var item in AppQuery)
                        {
                            rowsList.Add("['" + item.Researcher_Name + "', " + item.Number_of_Submissions + "]");
                        };
                        rows = String.Join(", ", rowsList);

                    }


                    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
                    <script type="text/javascript">
                        google.load("visualization", "1.1", { packages: ["table"] });
                        google.setOnLoadCallback(drawTable);


                        function drawTable() {

                            var data = google.visualization.arrayToDataTable([
                              ['Researcher Name', 'Number of Submissions'],
                               @Html.Raw(rows)]);

                            var options = {
                                title: ''
                            };

                            var dashboard = new google.visualization.Dashboard(document.querySelector('#dashboard'));

                            var stringFilter = new google.visualization.ControlWrapper({
                                controlType: 'StringFilter',
                                containerId: 'string_filter_div',
                                options: {
                                    filterColumnIndex: 0
                                }
                            });

                            var table = new google.visualization.ChartWrapper({
                                chartType: 'Table',
                                containerId: 'table_div',
                                options: {
                                    showRowNumber: false
                                }
                            });

                            dashboard.bind([stringFilter], [table]);
                            dashboard.draw(data);
                        }
                        google.load('visualization', '1', { packages: ['controls'], callback: drawTable });
                    </script>

                    <div id="dashboard">
                        <div id="string_filter_div"></div>
                        <div id="table_div"></div>
                    </div>

I feel like I am missing something really simple, but cannot rap my head around it.

Answer 1

First of all you don't need a GROUP BY most probably and so remove the line GROUP BY Submission.Status, Submission.Title; probably you don't need GROUP BY and rather meant to use ROW_NUMBER() function and your JOINS are total weird, modify them like below

SELECT * FROM (
SELECT Submission.Title AS [Title], 
Submission.Status AS [Submission_Status],
ROW_NUMBER() OVER(PARTITION BY Submission.Title ORDER BY Submission.Title) AS rn
FROM Submission 
INNER JOIN ResearcherSubmission ON Submission.SubmissionID = ResearcherSubmission.SubmissionID
INNER JOIN Researcher ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID
INNER JOIN School ON School.SchoolID = Researcher.SchoolID
INNER JOIN Faculty ON Faculty.FacultyID = School.[FacultyID] ) xx
WHERE rn = 1;