problems in sql with join some rows

Question

1.00/5 (1 vote)

See more:

i have data in field in table like:

browser: ip:
chrome23 127.1.1.2
crome24 127.1.1.2
chrome25 127.1.1.2
firefox3 127.1.1.2
firefox18 127.1.1.2

i want dispaly data like this

browser ip
chrome count(3)
firefox count(2)

Posted 27-Feb-13 21:50pm

jiji2663

Updated 27-Feb-13 21:53pm

v3

Add a Solution

Comments

willington.d 28-Feb-13 4:14am

hi jiji... why all values in browser ends with some values(23, 24, 25..)? b'cos of that, we can not group the browser to count...

jiji2663 28-Feb-13 4:40am

that is version of browser's

willington.d 28-Feb-13 5:09am

hi jiji

then try the following...

select left(browser, 4), COUNT(1) as IP from test2 group by left(browser, 4)

5 solutions

Solution 6

Hi,

Check the following script

SQL

DECLARE @BrowserDtls TABLE(LoginDtls VARCHAR(100))

INSERT INTO @BrowserDtls (LoginDtls)
SELECT 'chrome23 127.1.1.2'
UNION ALL SELECT 'crome24 127.1.1.2'
UNION ALL SELECT 'chrome25 127.1.1.2'
UNION ALL SELECT 'firefox3 127.1.1.2'
UNION ALL SELECT 'firefox18 127.1.1.2'

-- Actual Data
SELECT LoginDtls FROM @BrowserDtls 

-- Required Data
SELECT CASE WHEN PATINDEX('%[0-9]%',LoginDtls)>1 THEN LEFT(LoginDtls,PATINDEX('%[0-9]%',LoginDtls)-1) ELSE LoginDtls END 'BrowserName',
COUNT(*) 'BrowserCount' 
FROM @BrowserDtls 
GROUP BY (CASE WHEN PATINDEX('%[0-9]%',LoginDtls)>1 THEN LEFT(LoginDtls,PATINDEX('%[0-9]%',LoginDtls)-1)
		  ELSE LoginDtls END)

Posted 28-Feb-13 5:32am

gvprabu

Comments

gvprabu 1-Mar-13 0:32am

Hi
Try like this also....

SELECT SUBSTRING(LoginDtls,0,patindex('%[0-9]%',LoginDtls)),count(LoginDtls) FROM @BrowserDtls group by SUBSTRING(LoginDtls,0,patindex('%[0-9]%',LoginDtls))

Solution 2

Here is a sample approach

SQL

DECLARE @T TABLE (Browser VARCHAR(100), IP VARCHAR(100))
INSERT INTO @T
SELECT 'chrome23', '127.1.1.2' UNION ALL
SELECT 'chrome24', '127.1.1.2' UNION ALL
SELECT 'chrome25', '127.1.1.2' UNION ALL
SELECT 'firefox3', '127.1.1.2' UNION ALL
SELECT 'firefox18', '127.1.1.2'

--SELECT * FROM @T

SELECT Browser, SUM([Count]) AS IPCount FROM
(
    SELECT 'Chrome' AS Browser, CASE WHEN Browser LIKE 'Chrome%' THEN 1 ELSE 0 END AS [Count] FROM @T
    UNION ALL
    SELECT 'firefox' AS Browser, CASE WHEN Browser LIKE 'firefox%' THEN 1 ELSE 0 END AS [Count] FROM @T
) X
GROUP BY X.Browser

Posted 27-Feb-13 23:10pm

__TR__

Solution 5

Try This:

SQL

CREATE Function udf_Name(@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @NumRange as varchar(50) = '%[0-9]%'
    While PatIndex(@NumRange, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, '')

    Return @Temp
End

Then Use Above UDF in Query like

SQL

Select BName,
COUNT(tbl.Ip) As CountValue
from
(
Select dbo.udf_Name(b.browser) BName,* from Browser b
) tbl
Group by tbl.BName

Posted 28-Feb-13 1:52am

Shobhana.n

Updated 28-Feb-13 5:42am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

S. M. Ahasan Habib · Accepted Answer · 2013-02-27T23:24:00

If that is 2 different fields in your table then

SQL

SELECT Browser, Count(ip) AS IpCount FROM [YourTable]
GROUP BY Browser;

if that is single field then

SQL

create table browsers(name varchar(255));

SQL

insert into Browsers(name) values('chrome23 127.1.1.2'), ('chrome24 127.1.1.2'), ('chrome25 127.1.1.2'), ('firefox3 127.1.1.2'), ('firefox18 127.1.1.2');
select * from Browsers;

SQL

select BrowserName, Count(*) as Total from(
select SubString(name, 0, patindex('%[0-9]%', name)) as browserName, name from browsers
) as b
Group By BrowserName

jiji2663 · Accepted Answer · 2013-03-02T20:09:00

SQL

select LEFT(Tbl_Vst.BrowserType,2) as 'Browser type',COUNT(LEFT(Tbl_Vst.BrowserType,2)) as counts from Tbl_Vst
where LEFT(Tbl_Vst.BrowserType,2)='ch' or LEFT(Tbl_Vst.BrowserType,2)='fi' or LEFT(Tbl_Vst.BrowserType,2)='ie' or LEFT(Tbl_Vst.BrowserType,2)='un'
group by LEFT(Tbl_Vst.BrowserType,2)