query formation & getting data 7 count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query formation & getting data 7 count

Author

Topic

svibuk
Yak Posting Veteran

62 Posts

Posted - 2012-04-04 : 08:40:56

i have the data as
data

1-AB-XY
2-OP-AB
1-AB-XY
1-MN-ZZ-AB
1-OP-AB
2-AB-XY
1-MN-ZZ-AB
1-OP-AB
1-MN-ZZ-AB

2) i need SQL query to get count of the column data like

1-AB - 7(count)
1-XY - 2(count)
2-OP - 1(count)
1-MN - 3(count)
1-ZZ - 3(count)
1-OP - 2(count)
2-AB - 2 (count)
2-XY - 1 (count)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-04 : 09:03:45

your data is in one column or multiple column ?

KH
[spoiler]Time is always against us[/spoiler]

svibuk
Yak Posting Veteran

62 Posts

Posted - 2012-04-04 : 09:06:03

one column

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-04 : 10:23:09

[code]
select ID, Data, Cnt = count(*)
from (
select ID = dbo.fnParseString(-1, '-', yourdatacol), Data
from yourtbl s
cross apply dbo.fnParseList('-', yourdatacol)
where RowID <> 1
) d
group by ID, Data
order by ID, Data
[/code]

using fnParseString & fnParseString from
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

svibuk
Yak Posting Veteran

62 Posts

Posted - 2012-04-05 : 01:32:26

tried to execute the query
but having difficulty in understanding the query

whts is datacol & data
as i said i have my data in only one column (summary)
i tried using the below query

select summary
from tbl
cross apply dbo.fnParseList('-', summary)

i am just getting the list with the data duplicated
i dont get as i need

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-06 : 03:31:02

datacol is the column in your table

data is the column return from the table function fnParseList

quote:
i am just getting the list with the data duplicated
i dont get as i need

run the entire query as a whole

KH
[spoiler]Time is always against us[/spoiler]

svibuk
Yak Posting Veteran

62 Posts

Posted - 2012-04-06 : 08:07:51

quote:
Originally posted by khtan

datacol is the column in your table

data is the column return from the table function fnParseList

quote:
i am just getting the list with the data duplicated
i dont get as i need

run the entire query as a whole

KH
[spoiler]Time is always against us[/spoiler]

i have a table[table1] with a field summary
which has data as
1-AB-XY
2-OP-AB
1-AB-XY
1-MN-ZZ-AB
1-OP-AB
2-AB-XY
1-MN-ZZ-AB
1-OP-AB
1-MN-ZZ-AB

with the above data hardcoded in the below sample i get the desired result
(No column name) (No column name)
1-AB 7
1-MN 3
1-OP 2
1-XY 2
1-ZZ 3
2-AB 2
2-OP 1
2-XY 1

/*
CREATE FUNCTION dbo.split(
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
*/
DECLARE @TAB TABLE (SUMMARY VARCHAR(100))

INSERT INTO @TAB
SELECT '1-AB-XY'
UNION ALL SELECT '2-OP-AB'
UNION ALL SELECT '1-AB-XY'
UNION ALL SELECT '1-MN-ZZ-AB'
UNION ALL SELECT '1-OP-AB'
UNION ALL SELECT '2-AB-XY'
UNION ALL SELECT '1-MN-ZZ-AB'
UNION ALL SELECT '1-OP-AB'
UNION ALL SELECT '1-MN-ZZ-AB'

SELECT id + '-' + val, count(1)
FROM (
SELECT LEFT(SUMMARY, CHARINDEX('-', SUMMARY,1)-1) AS id, val
FROM @TAB CROSS APPLY dbo.split(SUBSTRING(SUMMARY,CHARINDEX('-', SUMMARY,1)+1 ,100), '-')
) TAB
GROUP BY id + '-' + val

but instead if i directly need to get the value from my exixting column i dont get the error

Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.

if i just use the query i get teh aboe error

SELECT id + '-' + val, count(1)
FROM (
SELECT LEFT(SUMMARY, CHARINDEX('-', SUMMARY,1)-1) AS id
--, val
FROM TABLE1 CROSS APPLY dbo.split(SUBSTRING(SUMMARY,CHARINDEX('-', SUMMARY,1)+1 ,100), '-')
) TABLE1
GROUP BY id + '-' + val

svibuk
Yak Posting Veteran

62 Posts

Posted - 2012-04-07 : 03:11:59

quote:

Originally posted by khtan


select	ID, Data, Cnt = count(*)
from	(
		select	ID = dbo.fnParseString(-1, '-', yourdatacol), Data
		from	yourtbl s
			cross apply dbo.fnParseList('-', yourdatacol)
		where	RowID	<> 1
	) d
group by ID, Data
order by ID, Data


i am not able to use the above query from my asp.net page 

i a m getting some unwanted charc in betwen the sql statement
select ID, ID+'-'+Data as ICARD, TOTAL = count(*)  from\t(select\tID = dbo.fnParseString(-1, '-', SUMMARY), Datafrom\tTABLE s cross apply dbo.fnParseList('-', SUMMARY)) d   group by ID, Data order by TOTAL desc

using fnParseString & fnParseString from
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources