|
Sorry,
I wrote that a little quickly. Yes, I forgot the GROUP BY, but the main problem was I reversed the references on the join . Try this (I tested it this time  ):
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 LIKE '%' + T1.Col1 + '%'
group by T1.Col1
|
|
|
|
|
hi Mark
i'm sorry but is still doesn't work i try both sql
and return me 0 rows
SELECT T1.Col, COUNT(*) FROM tbl1 T1
INNER JOIN tbl2 T2 ON T2.col LIKE '%' + T1.col + '%'
group by T1.Col
SELECT T1.Col, COUNT(*) FROM tbl1 T1
INNER JOIN tbl2 T2 ON T1.Col LIKE '%' + T2.Col + '%'
group by T1.Col
i also try this 2 sql sentences and is stil doesn't work
its return me
1000 0
1001 0
etc....
1)
select t1.col ,(SELECT count(*) FROM tbl2 t2
WHERE t1.col in (select t2.col from tbl2 t2))
From tbl1 t1
2)
select tbl1.col ,count(case when CHARINDEX(tbl1.col ,tbl2.col) > 0 then 1 else null end)
from tbl1,tbl2
group by tbl1.col
by the way i work with sql server 2005
thanks again
waiting for rescue
|
|
|
|
|
I'm running 2005 also. This script:
IF OBJECT_ID('Table1') IS NOT NULL
DROP TABLE Table1
IF OBJECT_ID('Table2') IS NOT NULL
DROP TABLE Table2
go
CREATE TABLE Table1(Col1 Varchar(15))
CREATE TABLE Table2(Col2 VARCHAR(100))
INSERT INTO Table1 VALUES('1000')
INSERT INTO Table1 VALUES('1001')
INSERT INTO Table1 VALUES('1002')
INSERT INTO Table2 VALUES('A1000A')
INSERT INTO Table2 VALUES('bbb1000bcd')
INSERT INTO Table2 VALUES('cdf1000frg')
INSERT INTO Table2 VALUES('A1001a')
INSERT INTO Table2 VALUES('bvc1001dcfe')
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 LIKE '%' + T1.Col1 + '%'
GROUP BY T1.Col1
Gives me these results:
1000 3
1001 2
If this isn't working for you, you'll need to give me your schema and what the results are (or the error messages).
|
|
|
|
|
thanks mark is work fine when the column type is varchar
but when the type is nchar it's doesnt work becuse it's fix length
roni vars
|
|
|
|
|
hi mark
You realy help in this Query
You are like Books Online
thanks again for your important help
and if it is nchar column type need to use ltrim rtrim function
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON ltrim(rtrim(T2.Col2)) LIKE '%' + ltrim(rtrim(T1.Col1)) + '%'
GROUP BY T1.Col1
regards
roni vars
|
|
|
|
|
sory my mistake i try this Sentence instead of first sentence i wrote before
select t1.col ,(SELECT count(*) FROM tbl2 t2 WHERE t1.col like '%'+ t2.col +'%') as count1
From tbl1 t1
roni vars
|
|
|
|
|
Hi,
I am having some trouble with an sql statement and im just wondering if anyone could help me I have a ScoutingReport Table that stores player scouting info such as the matched, coached and playerId and I need a count of the amount of times a player has been scouted but if the same scout reports on the same match twice then I must only be counted once hers what I have started with any help would be appreciated.
Select Count(Distinct(MatchId))
From ScoutingReport
where ScoutedPersonId = 10002
group by MatchId
order by MatchId
Thanks in advance
Tim
|
|
|
|
|
Try:
select ScotedPersonId, count(distinct MatchId)
from ScotingReport
group by ScoutedPersonId
order by ScoutedPersonId
|
|
|
|
|
thats what i need so far but i should also have said that if a different scout reports on the same match then it should be counted as 2 reports any ideas
thanks for your reply
|
|
|
|
|
Which database are you using? You may be able to get away with:
select ScoutedPersonId,
count(distinct convert(varchar, MatchId) + '-' + convert(varchar, ScoutId))
from ScotingReport
group by ScoutedPersonId
order by ScoutedPersonId (which cheats a bit and turns the MatchId and ScoutId into a single string value that can be counted) or
select ScoutedPersonId, Sum(ScoutCount)
from (
select ScoutedPersonId, MatchId, count(distinct ScoutId) AS ScoutCount
from ScoutingReport
group by ScoutedPersonId, MatchId
) A
group by ScoutedPersonId
order by ScoutedPersonId (which uses an inline view).
Regards
Andy
|
|
|
|
|
both worked perfectly
ur an absolute gentleman thanks alot man much appreciated
|
|
|
|
|
hi i am using this query to count the amount of team scout reports but when a player has no reports null is returned but i need 0 to be returned this is the sql any help would be appreciated
select sr.ScoutedPersonId, count(distinct convert(varchar, sr.MatchId) + '-' + convert(varchar, sr.ScoutId)) as 'IndCount',
(
-------------------
this is the team count that is not working
select
CASE
When count(distinct convert(varchar, MatchId) + '-' + convert(varchar, ScoutId)) > 0
Then count(distinct convert(varchar, MatchId) + '-' + convert(varchar, ScoutId))
Else 0
END
from ScoutingReport
Where Type = 1 -- team scout reports
And ScoutedPersonId = sr.ScoutedPersonId
group by ScoutedPersonId
---------------
) as 'TeamCount'
from ScoutingReport sr
Join Person p on p.ID = sr.ScoutedPersonId
Where sr.Type = 0
Group By sr.ScoutedPersonId
Having count(distinct convert(varchar, sr.MatchId) + '-' + convert(varchar, sr.ScoutId)) = 1
Order By sr.ScoutedPersonId
|
|
|
|
|
Hi all, i have a few quetion to discover, bee so pleasant to explain me how i can do:
1)
I have combobox and data source for one
this.comboBox1.DataSource = this.authorsBindingSource;
Now I need to add to combobox an row, what not exist in bindingSource (supoose I want do display in combobox an additional row "not defined")
How I can do this????
---------------------------------------------------------------
2)
How I can to mark a rows in dataTable, so that ones not will be stored in database then this dataTable updated.
I mean:
//myDataTable - DataTable object
// I want that this row shouldn’t bee stored in database, but exist in //dataTable (what I should do)
DataRow dr1 = myDataTable.NewRow();
///initilize row columns with data
..
myDataTable.Rows.Add(dr1);
// I want that this row should bee stored in database and exist in //dataTable (rest all as is)
DataRow dr2 = myDataTable.NewRow();
///initilize row columns with data
..
myDataTable.Rows.Add(dr2);
//Want to bee stored dr2 not dr1
this.myDataTableAdapter.Update(myDataTable);
How I can do this.
-----------------------------------------------------------------------------------
3) How I can explore what column (DataColumn type) have default value???
|
|
|
|
|
Hi ..
I a table ,, which has integer field "ID" ,,
I'd like order ID on Sql statment as a string ..
for eg ..
fields is
1 , 2 , 101 , 103 , 201 , 202
the order of fields will be :
1 , 2 , 101 , 103 , 201 , 202
but if that fields is String the order will be :
1 , 101 , 103 , 2 , 201 , 202
it's that I want ....
then how can I on the sql statment order an Integer field as String field
thanks for my favorite forum ... ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
jooooo
|
|
|
|
|
select cast(orderid as varchar(10)) from table1 order by cast(orderid as varchar(10))
use the above query u can get what ever output ur saying.
|
|
|
|
|
so thanks my friend
it's ok
jooooo
|
|
|
|
|
Unusual requirement! Order by Convert(varchar(10), ID)
|
|
|
|
|
thanks my friend ,,
your methos also is good
jooooo
|
|
|
|
|
We have sql server with the name production1 (IPID:100.102.33.33) ,from asp.net iam able to connect properly ,yesterday we changed the ip Id of this system(production1) from that time if iam trying to connect from asp.net code it is giving error sqlserver does not exists access denied.
Iam able to connect in query analyzer for the same sql server by using same userid,password.
Iam able to connect in vb.net for the same sql server with same connection string which iam using in asp.net
Iam unable to connect in asp.net error sqlserver does not exists access denied.
Please help me is there any problem with asp user or what?
Thanks
|
|
|
|
|
i had similar problem, unable to connect to sqlserver from query analyser or other application (cilent & server diff machines)
after creating an Alias to server machine using "Client Network Utility" of SQL Server able to crack this problem.
Hope this will solve your problem also
|
|
|
|
|
Hi,
I tried like that also but no luck.
Thanks,
J.ASwani kumar
|
|
|
|
|
I have a table where each row list's the product id and then 9 individual statistical percentage value relevant to that product. I need to create a final column to average all percentages into one overall score as well.
Any suggestions on how to do this?
The main select part of this code is below. Thanks in advance
SELECT ProductID, <br />
-- Calculate Speed percentage<br />
CONVERT(decimal(18, 2), @BestSpeed / (SELECT AVG(Finalised) FROM Leads WHERE Finalised IS NOT NULL AND ProductID = P.ProductID) * 100) As [Speed],<br />
--Actual speed value<br />
(SELECT AVG(Finalised) FROM Leads WHERE Finalised IS NOT NULL AND ProductID = P.ProductID) As [Actual Speed],<br />
-- Calculate APR value percentage<br />
CONVERT(decimal(18, 2), @BestAPR / (SELECT AVG(APR) FROM Leads WHERE APR IS NOT NULL AND ProductID = P.ProductID) * 100) As [APR],<br />
-- Actual APR value<br />
(SELECT AVG(APR) FROM Leads WHERE APR IS NOT NULL AND ProductID = P.ProductID) As [Actual APR],<br />
-- Calculate Broker Fee percentage<br />
CONVERT(decimal(18, 2), @BestBrokerFee / (SELECT AVG(BrokerFee) FROM Leads WHERE BrokerFee IS NOT NULL AND ProductID = P.ProductID) * 100) As [BrokerFee],<br />
-- Actual APR value<br />
(SELECT AVG(BrokerFee) FROM Leads WHERE BrokerFee IS NOT NULL AND ProductID = P.ProductID) As [Actual BrokerFee],
|
|
|
|
|
Try
select A.*,
([Speed] + [Average Speed] + ##Others##) / 9 AS FinalAverage
from (
##Your SQL goes here
) A
order by ProductId Your original SQL is used as an "inline view". The average is the total of your 9 columns, divided by nine.
Regards
Andy
|
|
|
|
|
I don't think I made myself completely clear. I do not have a table in my database with the statistics in them. I have a large query that is building these stats up as I go, for each row. What I want to do is average up these created values once the row is has all but finished. This should be the final column.
The code your provided does the correc thing but I need to know how to get the values from the row I am creating, rather than from a table in the database?
Any ideas?
Thanks
|
|
|
|
|
If you insert you original select statement where I indicated then you will find the the resultset contains all of your columns (thats the A.* bit), followed by your new average.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
