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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 get result using Count() and Sum()
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sadhu
Starting Member

14 Posts

Posted - 04/25/2013 :  05:39:45  Show Profile  Reply with Quote
I have a table with column and data as mentioned below :
PHONE
1
1
1
0
1
0
1
0
0

I want the output of the query to be a table having the columns and data as below:
PHONE(TotalCount) - 9
PHONE(Number of Distinct Values) - 2
PHONE(Distinct Values) - 0 and 1
PHONE(Count Of Each Distinct Value) - 4 and 5


Tried somany methods..but not able to comibine the result for both SUM and COUNT

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/25/2013 :  05:54:02  Show Profile  Reply with Quote

SELECT COUNT(PHONE) AS TotalCount,
COUNT(DISTINCT PHONE) AS DistinctValues,
STUFF((SELECT DISTINCT ',' + CAST(PHONE AS varchar(5)) FROM YourSourceTable ORDER BY ',' + CAST(PHONE AS varchar(5)) FOR XML PATH(''),1,1,'') AS DistValues,
STUFF((SELECT ',' + CAST(COUNT(*) AS varchar(5)) FROM YourSourceTable GROUP BY PHONE ORDER BY PHONE FOR XML PATH(''),1,1,'') AS CountDistValues INTO TableName
FROM YourSourceTable


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Sadhu
Starting Member

14 Posts

Posted - 04/25/2013 :  06:03:55  Show Profile  Reply with Quote
Thank you visakh16. Tried the query given above..but getting error messages as :

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '1'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'ORDER'.

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 04/25/2013 :  06:04:46  Show Profile  Reply with Quote
--some fixes for above query
SELECT COUNT(PHONE) AS TotalCount,
COUNT(DISTINCT PHONE) AS DistinctValues,
STUFF((SELECT DISTINCT ',' + CAST(PHONE AS varchar(5)) FROM @tab ORDER BY ',' + CAST(PHONE AS varchar(5)) FOR XML PATH('')),1,1,'') AS DistValues,
STUFF((SELECT ',' + CAST(COUNT(*) AS varchar(5)) FROM @tab GROUP BY PHONE ORDER BY PHONE FOR XML PATH('')),1,1,'') AS CountDistValues
INTO NewTable
FROM @tab

SELECT * FROM NewTable
NOTE: @tab is your actual table name
--
Chandu

Edited by - bandi on 04/25/2013 06:08:01
Go to Top of Page

Sadhu
Starting Member

14 Posts

Posted - 04/25/2013 :  06:17:31  Show Profile  Reply with Quote
Thanks for the reply Chandu and Vishak..that fixed the issue but I want the result to be displayed in different rows (in my case two rows) with the distinct values in each row instead of a comma seperated values as shown below..Please help.

(TotalCount,DistinctValues,DistValues,CountDistValues)
(9,2,0,4)
( , ,1,5)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 04/25/2013 :  06:33:56  Show Profile  Reply with Quote
--Is it okay?

select T.*, LEN(DistValues)-LEN(replace(DistValues, ',', ''))+1 DistValCount
from (SELECT DISTINCT COUNT(*) OVER() TotalCount
		,COUNT(*) OVER(PARTITION BY PHONE) NoOfDistCount
		,STUFF((SELECT ',' + CAST(PHONE AS VARCHAR) FROM @tab GROUP BY PHONE FOR XML PATH('')), 1, 1, '') DistValues
	   FROM @tab
	)T


--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 04/25/2013 :  06:44:02  Show Profile  Reply with Quote
SELECT * 
FROM (SELECT COUNT(PHONE) TotalCount ,COUNT(DISTINCT PHONE) NoOfDistCount From @tab )t1
	,(SELECT PHONE, COUNT(PHONE) TotalCount FROM @tab GROUP  BY PHONE ) t


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/25/2013 :  06:52:05  Show Profile  Reply with Quote
quote:
Originally posted by Sadhu

Thanks for the reply Chandu and Vishak..that fixed the issue but I want the result to be displayed in different rows (in my case two rows) with the distinct values in each row instead of a comma seperated values as shown below..Please help.

(TotalCount,DistinctValues,DistValues,CountDistValues)
(9,2,0,4)
( , ,1,5)



you mean blanks for first two columns in second row?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Sadhu
Starting Member

14 Posts

Posted - 04/25/2013 :  06:58:18  Show Profile  Reply with Quote
That's perfect..Thanks a ton!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 04/25/2013 :  07:04:42  Show Profile  Reply with Quote
quote:
Originally posted by Sadhu

That's perfect..Thanks a ton!


Welcome

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/25/2013 :  07:19:55  Show Profile  Reply with Quote
why do you need a cross join for that?

wont below suffice?


SELECT TotalCount,SUM(MaxColFlg) OVER () AS DistCount, PHONE,COUNT(PHONE) PhCount 
FROM 
(
SELECT *,
COUNT(PHONE) OVER () AS TotalCount,
CASE WHEN MAX(Col) OVER (PARTITION BY PHONE) = Col THEN 1 ELSE 0 END AS MaxColFlg
FROM @tab
)t 
GROUP  BY PHONE,TotalCount 


Col will be unique valued column (id field or datetime field)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 04/25/2013 :  07:33:43  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

why do you need a cross join for that?

wont below suffice?


SELECT TotalCount,SUM(MaxColFlg) OVER () AS DistCount, PHONE,COUNT(PHONE) PhCount 
FROM 
(
SELECT *,
COUNT(PHONE) OVER () AS TotalCount,
CASE WHEN MAX(Col) OVER (PARTITION BY PHONE) = Col THEN 1 ELSE 0 END AS MaxColFlg
FROM @tab
)t 
GROUP  BY PHONE,TotalCount 


Col will be unique valued column (id field or datetime field)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

It will ask for MaxColFlg in GROUP BY clause.
So DistCount will be incorrect

--
Chandu

Edited by - bandi on 04/25/2013 07:36:04
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/25/2013 :  08:34:53  Show Profile  Reply with Quote
I think it should still work fine (Not near a SQL box now so cant test this)

SELECT TotalCount,DistCount,PHONE,COUNT(PHONE) PhCount 
FROM
(
SELECT TotalCount,SUM(MaxColFlg) OVER () AS DistCount, PHONE
FROM 
(
SELECT *,
COUNT(PHONE) OVER () AS TotalCount,
CASE WHEN MAX(Col) OVER (PARTITION BY PHONE) = Col THEN 1 ELSE 0 END AS MaxColFlg
FROM @tab
)t 
)r
GROUP  BY PHONE,TotalCount,DistCount


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Sadhu
Starting Member

14 Posts

Posted - 04/26/2013 :  00:45:38  Show Profile  Reply with Quote
quote:
Originally posted by bandi

SELECT * 
FROM (SELECT COUNT(PHONE) TotalCount ,COUNT(DISTINCT PHONE) NoOfDistCount From @tab )t1
	,(SELECT PHONE, COUNT(PHONE) TotalCount FROM @tab GROUP  BY PHONE ) t


--
Chandu



One more problem..this query is not working if have have more than one column similar to the PHONE column.If I add another column to the query, it is inserting two more rows, instead I need to get the same result as PHONE next to the PHONE column in two rows.For Example

SELECT * FROM (SELECT COUNT(PHONE) [PHONE (TC)] ,COUNT(DISTINCT PHONE) [PHONE (Dist)] ,COUNT(AFFL_IN) [AFFL_IN (TC)] ,COUNT(DISTINCT AFFL_IN) [AFFL_IN (Dist)] From TableName )t1,(SELECT PHONE as [PHONE (Value)], COUNT(PHONE) [PHONE (Count)] ,AFFL_IN as [AFFL_IN (Value)], COUNT(AFFL_IN) [AFFL_IN (Count)] FROM TableName GROUP BY PHONE,AFFL_IN ) t
Go to Top of Page

Sadhu
Starting Member

14 Posts

Posted - 04/26/2013 :  00:46:50  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

why do you need a cross join for that?

wont below suffice?


SELECT TotalCount,SUM(MaxColFlg) OVER () AS DistCount, PHONE,COUNT(PHONE) PhCount 
FROM 
(
SELECT *,
COUNT(PHONE) OVER () AS TotalCount,
CASE WHEN MAX(Col) OVER (PARTITION BY PHONE) = Col THEN 1 ELSE 0 END AS MaxColFlg
FROM @tab
)t 
GROUP  BY PHONE,TotalCount 


Col will be unique valued column (id field or datetime field)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




I do not have any UNIQUE column for the table.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 04/26/2013 :  00:52:38  Show Profile  Reply with Quote
Result depends on GROUP BY PHONE,AFFL_IN...
Post us the complete requirement and sample data as well as expected output

--
Chandu
Go to Top of Page

Sadhu
Starting Member

14 Posts

Posted - 04/26/2013 :  01:25:56  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Result depends on GROUP BY PHONE,AFFL_IN...
Post us the complete requirement and sample data as well as expected output

--
Chandu



Table :

PHONE_IN |AFFL_IN |STATE|
1| 0| NJ
1| 0| WA
0| 0| CA
1| 1| TX
0| 1| TX
1| 0| FL
0| 0| FL
0| 2| MA
1| 0| NY
1| 0| CA
1| 2| CA

SAMPLE OUTPUT :

(PHONE_IN (TC),PHONE_IN (Dist),PHONE_IN (Value),PHONE_IN(Count),AFFL_IN (TC),AFFL_IN (Dist),AFFL_IN (Value),AFFL_IN (Count),STATE (TC),STATE (Dist),STATE (Value),STATE (Count))
(PHONE_IN (TC),PHONE_IN (Dist),PHONE_IN (Value),PHONE_IN (Count),AFFL_IN (TC),AFFL_IN (Dist),AFFL_IN (Value),AFFL_IN (Count),STATE (TC),STATE (Dist),STATE (Value),STATE (Count))
(11,2,0,4,11,3,0,7,11,7,CA,3)
( , ,1,7, , ,1,2, , ,FL,2)
( , , , , , ,2,2, , ,MA,1)
( , , , , , , , , , ,NJ,1)
( , , , , , , , , , ,NY,1)
( , , , , , , , , , ,TX,2)
( , , , , , , , , , ,WA,2)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/26/2013 :  01:56:02  Show Profile  Reply with Quote
The display format of output looks a bit weird. Anyways i think below should work


;With CTE
AS
(
SELECT DISTINCT PHONE_TC,
SUM(CASE WHEN Seq_PHONE=1 THEN 1 ELSE 0 END) OVER () AS PHONE_Dist,
AFFL_IN_TC,
SUM(CASE WHEN Seq_AFFL_IN=1 THEN 1 ELSE 0 END) OVER () AS AFFL_IN_Dist,
STATE_TC,
SUM(CASE WHEN Seq_STATE=1 THEN 1 ELSE 0 END) OVER () AS STATE_Dist
FROM
(
SELECT COUNT(PHONE_IN) OVER () AS PHONE_TC,
COUNT(AFFL_IN) OVER () AS AFFL_IN_TC,
COUNT(STATE) OVER () AS STATE_TC,
ROW_NUMBER() OVER (PARTITION BY PHONE_IN ORDER BY (SELECT 1)) AS Seq_PHONE,
ROW_NUMBER() OVER (PARTITION BY AFFL_IN ORDER BY (SELECT 1)) AS Seq_AFFL_IN,
ROW_NUMBER() OVER (PARTITION BY STATE ORDER BY (SELECT 1)) AS Seq_STATE
FROM Table
)t
)

SELECT c.PHONE_TC,
c.PHONE_Dist,
p.PHONE_IN,
p.Cnt,
c.AFFL_IN_TC,
c.AFFL_IN_Dist,
a.AFFL_IN,
a.Cnt,
c.STATE_TC,
c.STATE_Dist,
s.STATE,
s.Cnt
FROM (SELECT 1 AS Rn,* FROM CTE) c
FULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY PHONE_IN) AS Rn,PHONE_IN,COUNT(*) AS Cnt
           FROM Table
           GROUP BY PHONE_IN
          )p
ON p.Rn = c.Rn
FULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY AFFL_IN ) AS Rn,AFFL_IN ,COUNT(*) AS Cnt
           FROM Table
           GROUP BY AFFL_IN 
          )a
ON a.Rn = p.Rn
FULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY STATE) AS Rn,STATE,COUNT(*) AS Cnt
           FROM Table
           GROUP BY STATE
          )s
ON s.Rn = a.Rn



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 04/26/2013 01:57:06
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 04/26/2013 :  01:58:44  Show Profile  Reply with Quote
-- Is it okay?

SELECT DISTINCT Phone PhoneVal, COUNT(*) OVER(Partition by Phone) phoneCount, 
	AFFL_IN AfflVal, COUNT(*) OVER(Partition by affl_in) AfflInCount, 
	STATE StateVal, COUNT(*) OVER(Partition by state) StateCount,
	COUNT(*) OVER() OverallCount
FROM TableName


NOTE: this won't give exact your output
--
Chandu

Edited by - bandi on 04/26/2013 02:05:13
Go to Top of Page

Sadhu
Starting Member

14 Posts

Posted - 04/26/2013 :  02:32:59  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

The display format of output looks a bit weird. Anyways i think below should work


;With CTE
AS
(
SELECT DISTINCT PHONE_TC,
SUM(CASE WHEN Seq_PHONE=1 THEN 1 ELSE 0 END) OVER () AS PHONE_Dist,
AFFL_IN_TC,
SUM(CASE WHEN Seq_AFFL_IN=1 THEN 1 ELSE 0 END) OVER () AS AFFL_IN_Dist,
STATE_TC,
SUM(CASE WHEN Seq_STATE=1 THEN 1 ELSE 0 END) OVER () AS STATE_Dist
FROM
(
SELECT COUNT(PHONE_IN) OVER () AS PHONE_TC,
COUNT(AFFL_IN) OVER () AS AFFL_IN_TC,
COUNT(STATE) OVER () AS STATE_TC,
ROW_NUMBER() OVER (PARTITION BY PHONE_IN ORDER BY (SELECT 1)) AS Seq_PHONE,
ROW_NUMBER() OVER (PARTITION BY AFFL_IN ORDER BY (SELECT 1)) AS Seq_AFFL_IN,
ROW_NUMBER() OVER (PARTITION BY STATE ORDER BY (SELECT 1)) AS Seq_STATE
FROM Table
)t
)

SELECT c.PHONE_TC,
c.PHONE_Dist,
p.PHONE_IN,
p.Cnt,
c.AFFL_IN_TC,
c.AFFL_IN_Dist,
a.AFFL_IN,
a.Cnt,
c.STATE_TC,
c.STATE_Dist,
s.STATE,
s.Cnt
FROM (SELECT 1 AS Rn,* FROM CTE) c
FULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY PHONE_IN) AS Rn,PHONE_IN,COUNT(*) AS Cnt
           FROM Table
           GROUP BY PHONE_IN
          )p
ON p.Rn = c.Rn
FULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY AFFL_IN ) AS Rn,AFFL_IN ,COUNT(*) AS Cnt
           FROM Table
           GROUP BY AFFL_IN 
          )a
ON a.Rn = p.Rn
FULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY STATE) AS Rn,STATE,COUNT(*) AS Cnt
           FROM Table
           GROUP BY STATE
          )s
ON s.Rn = a.Rn



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thank You very much..this is exactly what I am looking for..thank you so much!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/26/2013 :  02:38:51  Show Profile  Reply with Quote
quote:
Originally posted by Sadhu

quote:
Originally posted by visakh16

The display format of output looks a bit weird. Anyways i think below should work


;With CTE
AS
(
SELECT DISTINCT PHONE_TC,
SUM(CASE WHEN Seq_PHONE=1 THEN 1 ELSE 0 END) OVER () AS PHONE_Dist,
AFFL_IN_TC,
SUM(CASE WHEN Seq_AFFL_IN=1 THEN 1 ELSE 0 END) OVER () AS AFFL_IN_Dist,
STATE_TC,
SUM(CASE WHEN Seq_STATE=1 THEN 1 ELSE 0 END) OVER () AS STATE_Dist
FROM
(
SELECT COUNT(PHONE_IN) OVER () AS PHONE_TC,
COUNT(AFFL_IN) OVER () AS AFFL_IN_TC,
COUNT(STATE) OVER () AS STATE_TC,
ROW_NUMBER() OVER (PARTITION BY PHONE_IN ORDER BY (SELECT 1)) AS Seq_PHONE,
ROW_NUMBER() OVER (PARTITION BY AFFL_IN ORDER BY (SELECT 1)) AS Seq_AFFL_IN,
ROW_NUMBER() OVER (PARTITION BY STATE ORDER BY (SELECT 1)) AS Seq_STATE
FROM Table
)t
)

SELECT c.PHONE_TC,
c.PHONE_Dist,
p.PHONE_IN,
p.Cnt,
c.AFFL_IN_TC,
c.AFFL_IN_Dist,
a.AFFL_IN,
a.Cnt,
c.STATE_TC,
c.STATE_Dist,
s.STATE,
s.Cnt
FROM (SELECT 1 AS Rn,* FROM CTE) c
FULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY PHONE_IN) AS Rn,PHONE_IN,COUNT(*) AS Cnt
           FROM Table
           GROUP BY PHONE_IN
          )p
ON p.Rn = c.Rn
FULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY AFFL_IN ) AS Rn,AFFL_IN ,COUNT(*) AS Cnt
           FROM Table
           GROUP BY AFFL_IN 
          )a
ON a.Rn = p.Rn
FULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY STATE) AS Rn,STATE,COUNT(*) AS Cnt
           FROM Table
           GROUP BY STATE
          )s
ON s.Rn = a.Rn



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thank You very much..this is exactly what I am looking for..thank you so much!


Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000