count and Show in new fields - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

count and Show in new fields

Author

Topic

Leo_Don
Starting Member

42 Posts

Posted - 2010-11-30 : 06:19:46

Hi Experts,

I have a table where it has different fields.

for eg: a person details like there first name and address are there in the table.
-------------------------------------------------------------
name | last name| street | city |
---------------------------------------------
x | y | as | ba
x | y | as | ba
e | d | wq | ba
x | y | Wq | ba
e | y | as | ba
------------------------------------------------

and i want the result as
-----------------------------------------------
street as| street wq| total
------------------------------------------------
2 | 1 | 3
------------------------------------------------

I just want to display the result by selecting the street, count and display as shown above.

Thanks in advance
-Leo

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-30 : 06:33:54

I dont know very much about your requirement.
But why dont you get result in below format
and do the formatting in frontend.

SELECT Street, Count(*) FROM TableName
GROUP BY Street WITH CUBE

Vaibhav T

If I cant go back, I want to go fast...

Sachin.Nand

2937 Posts

Posted - 2010-11-30 : 06:56:56

The OP wants to display the count of each distinct street names in cross tab format along with the total count.If that's the case then are those street names fixed or dynamic?

PBUH

Leo_Don
Starting Member

42 Posts

Posted - 2010-11-30 : 08:19:16

thank you guys

those are static (sorry not dynamic)

and if we have the name in a different table and would like to take the name from one table and street from other table then how can we do it the output should be as below

---------------------------------------------------
name | street as | street wq| total
---------------------------------------
x | 2 | 1 | 3 |
e | 1 | 1 | 2 |
----------------------------------------------

format is not important

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-30 : 08:23:39

You need dynamic pivot
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Leo_Don
Starting Member

42 Posts

Posted - 2010-11-30 : 08:44:19

any suggestions

Leo_Don
Starting Member

42 Posts

Posted - 2010-11-30 : 09:08:12

here is what i am trying to do

select userid , Add

from comm_id LEFT OUTER JOIN Comm ON co_coid = cm_onid

where (add = 'aaa' ) or (ass = 'bbb')or ( add = 'ccc')

i am getting the result as

----------------------------------------------
userid | add |
--------------------------
1| aaa
1| aaa
1| aaa
1| bbb
2| aaa
2| bbb
1| ccc

--------------------------------

rather i want a result of
------------------------------------------
userid | add aaa | add bbb | add ccc | total
-------------------------------------------
1 | 3 | 1 | 1 | 4
2 | --- | 1 | --- | 1
----------------------------------------------

Leo_Don
Starting Member

42 Posts

Posted - 2010-12-01 : 05:56:10

any suggestions!!!!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-01 : 06:20:09

I dont know whether your expected output is correct or not
but as per your sample data and your description

I think you want to get the count of add of different userid and want to show total also

in row to column format
try this -


DECLARE @User AS TABLE ( userid int, [add] varchar(10) )

INSERT INTO @User
SELECT '1','aaa'
UNION ALL
SELECT '1','aaa'
UNION ALL
SELECT '1','aaa'
UNION ALL
SELECT '1','bbb'
UNION ALL
SELECT '2','aaa'
UNION ALL
SELECT '2','bbb'
UNION ALL
SELECT '1','ccc'



SELECT UserID, [add aaa], [add bbb], [add ccc], [add aaa]+[add bbb]+[add ccc] AS Total FROM 
(
SELECT UserID, 'add ' + [add] [add] FROM @User
) A
PIVOT( COUNT([add]) FOR [add] IN ([add aaa], [add bbb], [add ccc]) ) P

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources