Create a pie chart that will count multiple columns sql vb.net

Question

So I'm creating a pie chart that will count the number of items in a column, the problem is it also counts the null items, i can make the query not count the nulls only on one column, it displays nothing when i make it not count the null on more than 1 column. And when a row has a value on multiple columns for example row 1 has a value on violation1 and violation2, there will be a portion in the pie chart that counts both of them as a new value.

here is my code -

    Dim cnn3 As New SqlConnection
    Dim cmd3 As New SqlCommand
    Dim dr As SqlDataReader
    Try
        cnn3.ConnectionString = Login.sqlcode
        cmd3.Connection = cnn3
        Dim tblFields As String = "SELECT count(*) as [Vio] from Violations WHERE Violation1<> '" & "NULL" & "' Group by Violation1, Violation2, Violation3, Violation4, Violation5, Violation6, Violation7, Violation8"
        Dim oData As New SqlDataAdapter(tblFields, cnn3)
        Dim ds As New DataSet
        Dim oCmd As New SqlCommand(tblFields, cnn3)
        cnn3.Open()
        oData.Fill(ds, "Violations")
        cnn3.Close()
        Chart1.DataSource = ds.Tables("Violations")
        Dim Series1 As Series = Chart1.Series("Series1")
        Series1.Name = "Violations"
        Chart1.Series(Series1.Name).YValueMembers = "Vio"
        Chart1.Size = New System.Drawing.Size(780, 350)
    Catch ex As Exception
        MessageBox.Show("Error while connecting to SQL Server." & ex.Message)
    End Try

I think the problem is in the query, what im trying to achieve here is for the pie chart to display the counts of column violation1-8, without nulls and without combining the values.

Answer 1

Here's a quick workup of what I'm describing.

Assume the table holds the following information:

By running the query:

SELECT Count(Violation1) AS Violation1,
       Count(Violation2) AS Violation2,
       Count(Violation3) AS Violation3,
       Count(Violation4) AS Violation4,
       Count(Violation5) AS Violation5
FROM Violations

We end up with the following results:

These results when put into a dataset makes it quite easy to display a graph.

Here we populate a graph using our Dataset we filled from our query result.

    Chart1.Series.Clear()
    Chart1.Series.Add("Series1")
    'we only have one row so we only need to loop through the columns
    'of our row (Rows(0))  
    For Each c As DataColumn In ds.Tables(0).Columns
        Chart1.Series(0).Points.AddXY(c.ColumnName, ds.Tables(0).Rows(0).Item(c.ColumnName).ToString)
    Next
    Chart1.Series(0).ChartType = DataVisualization.Charting.SeriesChartType.Pie
    Chart1.Series(0).IsValueShownAsLabel = True 'show the value of each "slice"

Leaving us with the following graph: