locked
Count on column in SQL RRS feed

  • Question

  • User639567535 posted

    When i try query data is like this 


    data from above query 

    VName	           (No column name)
    				1
    				1
    				1
    abc				1
    abc				1
    dfg				1
    dfg				1
    dfg				1
    dfg				1
    dfg				1
    dfg				1
    abc				1
    abc				1
    abc				1
    dfg				1
    abc				1
    abc				1
    DPSCB	     			1
    MV				1
    MV				1
    MV				1
    MV				1
    MV				1
    MV				1
    MV				1
    


    now i want data like this



    query which i tried

    Select tblVV.ID,VName,COUNT(VName) 
    from tblVV
    join tblRV on tblVV.MID = tblRV.ID
    group by
    VName,
    tblVV.ID 




    how to get data this data 

    abc     7
    dfg     7
    DPSCB   1
    MV      7
            3 (this is empty but i have to count this also )


    columns in tblvv

    ID MID VName Speed Name

    columns in tblrv

    ID FFID OName regno City

    Friday, June 24, 2016 9:40 AM

Answers

  • User753101303 posted

    Hi,

    What if you try :

    Select VName,COUNT(*) 
    from tblVV
    join tblRV on tblVV.MID = tblRegionVehicles_Uni.ID
    group by
    VName

    For now it seems the issue is that you include a ID column which is necessarily different on each row so you show each row with a 1 count.

    Edit: not even sure why you join with the other table. Is this needed? Anyway the point just be to include in your group by clause only the column(s) you want to group on.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 24, 2016 9:59 AM