SQL Server Forums
Profile | Register | 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()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sadhu
Starting Member

11 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
52317 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

11 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
2206 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

11 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
2206 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
2206 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
52317 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

11 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
2206 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
52317 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
2206 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
52317 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

11 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

11 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
2206 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

11 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
52317 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
2206 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

11 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
52317 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  
 New 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.12 seconds. Powered By: Snitz Forums 2000