Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Unanswered: MySQL Count Columns in my Database Files

    Can Anyone help me about this system thank you
    This is my Code

    Private Sub SubjectSchedule2()
    Dim str(4) As String
    ---> my query ExecuteQuery("Select *,(Select count(*) from studentsubject where labday='" & labdays & "' and labtime='" & labtimes & "' and labroom='" & labrooms & "') as myCount from studentsubject where StudentID='" & studID & "'")
    ListView2.Items.Clear()
    With reader
    If .HasRows = True Then
    While .Read
    str(0) = .Item("LabDay").ToString
    str(1) = .Item("LabTime").ToString
    str(2) = .Item("LabRoom").ToString
    str(3) = .Item("mycount").ToString
    Dim lv As New ListViewItem(str)
    ListView2.Items.Add(lv)
    End While
    End If
    End With
    Call disconnectDB()
    End Sub

    help i think my query is wrong....i want to fix it, thank you
    i want my Database(Listview) Count by Columns

    Heres My Example Listview
    Attached Thumbnails Attached Thumbnails Help.png  
    Last edited by shockwave1990; 04-27-13 at 21:55.

  2. #2
    Join Date
    Apr 2013
    Posts
    4

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    you should look at COUNT ... GROUP BY in your SQL.

    Code:
    SELECT labday, labtime, labroom, COUNT(*)
    FROM studentSubject
    WHERE studentID = studID
    GROUP BY labday, labtime, labroom;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Apr 2013
    Posts
    4
    yes it is the same as group by

    what i mean is i want to count in every column of my listview
    labday. labtime , labroom
    by it only corrects in the first column but in 2nd,3rd,4th,5th,6th columns failed

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You will need to give an example as I do not follow what you are asking for?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Apr 2013
    Posts
    4
    Ex. this is my mysql database

    ---------------------------------------------------------------
    Lab Day Lab Time Lab Room Count
    MON | 07:30-08:00AM | Comp. Lab 1
    MON | 07:30-08:00AM | Comp. Lab 2
    TUE | 09:00-10:30AM | Comp. Lab 2
    FRI | 01:00-03:00PM | Comp. Lab 1
    FRI | 01:00-03:00PM | Comp. Lab 1
    WED | 03:00-05:00PM | Comp. Lab 1
    WED | 03:00-05:00PM | Comp. Lab 1
    THU | 01:00-03:00PM | Comp. Lab 2
    ----------------------------------------------------------------

    if i run my vb.net system form_Load i will make my listview looks like this
    ---------------------------------------------------------------
    Lab Day Lab Time Lab Room Count
    MON | 07:30-08:00AM | Comp. Lab 1 1
    MON | 07:30-08:00AM | Comp. Lab 2 1
    TUE | 09:00-10:30AM | Comp. Lab 2 1
    FRI | 01:00-03:00PM | Comp. Lab 1 3
    WED | 03:00-05:00PM | Comp. Lab 1 2
    THU | 01:00-03:00PM | Comp. Lab 2 1
    ----------------------------------------------------------------
    Last edited by shockwave1990; 04-28-13 at 18:41.

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Did you run my query because that is exactly what is would return. There is an issue with your data you should have included an additional row for Friday.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •