Add values from several columns from two different tables?

[savedlema]

Hi!
I have two tables, I will call them first-half and second-half (of the year)

Table: first
id   jan  feb  mar  apr
-----------------------
2     80   90  70    60
3     50   40  60    30

Table: second
id   jan  feb  mar  apr
-----------------------
2     50   70  60    90
3     80   60  50    40

I want to do a SELECT that will bring a total of each individual values (jan, feb, mar, apr) from these tables. Fetch by id. I want something like this:

Table: total
id   jan  feb  mar  apr
-----------------------
2    130  160  130   150

How can I make this possible?
ALSO, I will be so thankful if, in that SELECT statement, I can specify the columns I want as comma separated values, that is, I will not always have to pull values of all columns (jan,feb,mar,apr) but I will choose which values I want (say jan,mar,apr) and this is specified as a result of another SELECT statement which will bring required columns, separated by commas (like: jan,mar,apr)

The goal then, is to generate a report (from VB.NET) that shows all this information from table first, second and total
Thank you.

[ddanbe]

How are your data structured, in a DataGridView an array, a file, something else?

[Santanu.Das]

You can use the following SQL Statement

Select A.id, 
(A.jan + B.jan) As JANUARY,
(A.feb + B.feb) As FEBRUARY,
(A.mar + B.mar) As MARCH,
(A.apr + B.apr) As APRIL
 From first A 
Inner Join second B
On A.id=B.id Order By A.id

[savedlema]

Hi Shark_1, that code works, but, do you have any idea how I can make the months inside the SELECT to be varying according to the users choice at run time?
For example, sometimes the user will have to leave out both March values. How can I have the program change the SELECT statement at runtime?

The values to be SELECTed will always be specified and in the form (jan,mar,apr) or (feb,mar,apr), now how can take these varrying values into the SELECT statement AT RUNTIME?

Thank you.

[Santanu.Das]

You can make your sql statement concatinating user choiced field names.

Here I just give some codes. I just use a Combobox to choose moth name.

Imports MySql.Data.MySqlClient

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        For i As Integer = 1 To 12
            ComboBox1.Items.Add(MonthName(i))
        Next
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim con As MySql.Data.MySqlClient.MySqlConnection = New MySql.Data.MySqlClient.MySqlConnection("Server=127.0.0.1;Port=3306;UID=root;Pwd=****;Database=TableName;")
        con.Open()

        Dim cmd As New MySqlCommand
        cmd.CommandText = "Select A.id, (A." & LCase(Mid(ComboBox1.SelectedItem, 1, 3)) & "+B." & LCase(Mid(ComboBox1.SelectedItem, 1, 3)) &
                            ") As " & ComboBox1.SelectedItem & " From first A Inner Join second B On A.id=B.id Order By A.id"
        cmd.Connection = con
        Dim rd As MySqlDataReader = cmd.ExecuteReader()

        If rd.HasRows() Then
            Dim x As String
            Do While rd.Read()
                x &= String.Format("{0}  {1}", rd("id"), rd(ComboBox1.SelectedItem)) & vbCrLf
            Loop

            MessageBox.Show(x)
        End If
        rd.Close()

        cmd.Dispose()
        con.Dispose()
    End Sub
End Class

[savedlema]

Hey Shark_1!
Thanks again for taking your time to assist with that useful code.
The code does work but sorry, the above code will only bring a total of one selected month, say "Jan" after a user select the month in the ComboBox1. But I would like to be able to pull several months altogether, something like the sample in my OP, like this:

    Table: total
    id   jan  feb   apr
    -----------------------
    2    130  160   150

It looks to me that, the ComboBox won't be helpful here since I will always have to select more than one columns (in fact, it will range from 3 - 10 columns in actual table).

Let me make it clear again that, the columns to be selected (whether they be 3, 4, 9 or whatever) will available in VB.NET as a string variable containing values in the form (jan, feb, apr......), I was wondering, for example, is it possible to Loop though this variable contents?

Thank you.

[Santanu.Das]

I will always have to select more than one columns (in fact, it will range from 3 - 10 columns in actual table).

I just give you an example, how you can get a field value from your table as per user's selection.
To do that you can use listbox for multiple selection of months in any combination.

[savedlema]

Hi Shark_1,

I tried this

ConnectionSettings()

        con.Open()
        Dim courses As String = "jan,mar"

        Dim cmd As New MySqlCommand
        cmd.CommandText = "Select A.sid, (A." & LCase((courses)) & "+B." & LCase((courses)) &
                            ") As " & courses & " From first A Inner Join second B On A.id=B.sid Order By A.id"
        cmd.Connection = con
        Dim rd As MySqlDataReader = cmd.ExecuteReader()

        If rd.HasRows() Then
            Dim x As String
            Do While rd.Read()
                x &= String.Format("{0}  {1}", rd("id"), rd(ComboBox2.SelectedItem)) & vbCrLf
            Loop
            MessageBox.Show(x)
        End If
        rd.Close()
        cmd.Dispose()
        con.Dispose()

I changed the user selection input from ComboBox1 to a string value (courses) containing the user selections, but this does not work, it says "the field mar is ambigous"
I first tried to put these values in a ListBox, and changed "ComboBox1" to "ListBox1" in a SELECT statement but also to no avail.
I know I must be missing something here.

Any idea please?

[Santanu.Das]

You already declared the table 'first' as 'A' and 'second' as 'B'. In SQL statement you must have to specify which 'mar' you are trying to call from databases 'A' or 'B' by prefixing A or B before mar.

Going through a loop you can construct your SQL statement easily.

Dim xsql() As String=split("jan,feb,mar",",")

Dim sqlstr as string="Select A.id,"
For i As Integer = 0 To 2
    sqlstr &="(A." & xsql(i) & "+B." & xsql(i) & ") As " & MonthName(i+1) & Iff(i<2,",","")

Next
sqlstr &= " From first A Inner Join second B On A.id=B.id Order By A.id"

The above example can finally produce the SQL Statement as

Select A.id,(A.jan+B.jan) As January,(A.feb+B.feb) As February,(A.mar+B.mar) As March From first A Inner Join second B On A.id=B.id Order By A.id

Now your final codes should be

con.Open()
        Dim courses As String = "jan,mar"
        Dim cmd As New MySqlCommand
         Dim xsql() As String=split("jan,feb,mar",",")

Dim sqlstr as string="Select A.id,"
For i As Integer = 0 To 2
    sqlstr &="(A." & xsql(i) & "+B." & xsql(i) & ") As " & MonthName(i+1) & Iff(i<2,",","")

Next
sqlstr &= " From first A Inner Join second B On A.id=B.sid Order By A.id"

        cmd.CommandText = sqlstr
        cmd.Connection = con
        Dim rd As MySqlDataReader = cmd.ExecuteReader()
        If rd.HasRows() Then
            Dim x As String
            dim y as integer=0
            Do While rd.Read()
            y +=1
                x &= String.Format("{0}  {1}", rd("id"), rd(MonthName(y)) & vbCrLf

            Loop
            MessageBox.Show(x)
        End If
        rd.Close()
        cmd.Dispose()
        con.Dispose()

Hope it can help you.

[savedlema]

Hi Shark_1,
Your post has been SO HELPFUL, Thanks a lot. It has worked perfectly.

Just two more questions, first, did you mean to use this line in your code? You declared the variable but it has never been used.

        `Dim courses As String = "jan,mar"`

But I have decided I can use it to specify what goes into xsql() variable, shouldn't be a problem I think.

And,to get more from tables, I also need to find & add another column for totals. This will hold calculated totals of values returned by the SELECT querry we have just constructed. That is, the SUM of all rows per each id.
To do that, I need to concatenate the SUM() querry to the querry we just made.

This is the code I want concatenated:

,SUM((A.jan+B.jan)+ (A.feb+B.feb) + (A.mar+B.mar)) As Total

I tried this:

            sqlstr &= "(A." & xsql(i) & "+B." & xsql(i) & ") As " & xsql(i) & If(i < 2, ",", "" & " ,SUM((A." & xsql(i) & "+B." & xsql(i) & If(i < 2, ",", "" & "))  As Total"))

But its only concatenating the last month, it does not do the same for other months in the array like the first part of the code. That's, the only thing that shows in the SUM section is SUM((A.mar+B.mar)) As Total

How can I concatenate this successully?

Thanks again.

[Santanu.Das]

Dim courses As String = "jan,mar" I copied it from your post, forgot it to remove , sorry. The above codes which I post written directly in DW. I had no way to examine it in my computer in that moment or to dry run the codes.

Your statement should be

Dim sqlstr as string="Select A.id,"
dim sqlsum as string=""
For i As Integer = 0 To 2
    sqlstr &="(A." & xsql(i) & "+B." & xsql(i) & ") As " & MonthName(i+1) & ","
    sqlsum &= "(A." & xsql(i) & "+B." & xsql(i) & ")" & IIf(i<2,"+","")
Next
sqlstr &= sqlsum & " As Total"
sqlstr &= " From first A Inner Join second B On A.id=B.sid Order By A.id"

[savedlema]

Thanks Shark_1,
That now works perfectly. I took a challenge and added another line to find Average. This is the part that computes the avg

            sqlavg &= "((A." & xsql(i) & "+B." & xsql(i) & ")/3)" & IIf(i < 2, "+", "") ' WORKS WELL

        Next
        sqlstr &= sqlsum & " As Total" & "," & sqlavg & "As Average"

Thank you so much.

[savedlema]

My next challenge is to grade the values returned as "total" in the code above to various grades, such as:
90-100 = A
80-90 = B
70-80 = C

I know we can use SELECT...CASE WHEN inside a mysql statement right? any idea please again?

[Santanu.Das]

Quite right, you can use case expression to do that.

[savedlema]

Shark_1,
I'm so glad I got it to also find the grade using CASE WHEN.
Thank you for all your help.