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
SQL
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

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
VName,
tblVV.ID

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

SQL
tblvv
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 
 
tblrv
<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>
Posted
Updated 24-Jun-16 0:07am
v6
Comments
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
   
NOW CHECck
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.
Try
SQL
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