Click here to Skip to main content
15,068,988 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
When i try query data is like this

data from above query
VName	           (No column name)
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

What I have tried:

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

how to get data this data
abc     7
dfg     7
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

ID     MID     VName     Speed Name
1	2	abc
2	3	abc
3	51	abc
4	67	
5	56	dfg
6	45	dfg
7	34	MV
8	24	MV <pre>

AND so on 
<pre lang="SQL">
ID        FFID    OName  regno     City
1	106	Peter	AJ-29	  uk
2	105	Max	AVL-91	 US
3	104	John	AVL-995	 US
4	148	M Jon P	AVL-97	 uk
5	109	sAR	AM-713	 US
6	102	dAVID	ASJ-9	 uk <pre>
Updated 24-Jun-16 0:07am
CHill60 24-Jun-16 4:48am
What data is on tblVV and tlbRV? Give samples of the original data not your query result
super_user 24-Jun-16 4:55am
check update please i post columns of both tables
CHill60 24-Jun-16 4:57am
And some sample data?...
super_user 24-Jun-16 5:33am
CHill60 24-Jun-16 5:48am
What is the link between tblVV and tblRV? In your query you are joining on a non-existent table tblRegionVehicles_Uni
super_user 24-Jun-16 5:59am
sorry this table tblRegionVehicles_Uni is same as tblRV .. check update and there is ID in both tables

1 solution

Your problem is that you have included ID in the GROUP BY.
Select VName,COUNT(VName) 
from tblVV
join tblRV on tblVV.MID = tblRV.ID
group by VName
ORDER BY ISNULL(VName, 'zzzz')

The isnull bit on the ORDER BY is just to get the null entry last

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900