get result using Count() and Sum()

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)

get result using Count() and Sum()

Author

Topic

Sadhu
Starting Member

14 Posts

Posted - 2013-04-25 : 05:39:45

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

52326 Posts

Posted - 2013-04-25 : 05:54:02

[code]
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
[/code]

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

Sadhu
Starting Member

14 Posts

Posted - 2013-04-25 : 06:03:55

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'.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-25 : 06:04:46

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

Sadhu
Starting Member

14 Posts

Posted - 2013-04-25 : 06:17:31

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)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-25 : 06:33:56

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-25 : 06:44:02

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

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-25 : 06:52:05

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

Sadhu
Starting Member

14 Posts

Posted - 2013-04-25 : 06:58:18

That's perfect..Thanks a ton!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-25 : 07:04:42

quote:
Originally posted by Sadhu

That's perfect..Thanks a ton!

Welcome

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-25 : 07:19:55

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-25 : 07:33:43

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-25 : 08:34:53

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

Sadhu
Starting Member

14 Posts

Posted - 2013-04-26 : 00:45:38

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

Sadhu
Starting Member

14 Posts

Posted - 2013-04-26 : 00:46:50

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-26 : 00:52:38

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

--
Chandu

Sadhu
Starting Member

14 Posts

Posted - 2013-04-26 : 01:25:56

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 01:56:02

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-26 : 01:58:44

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

Sadhu
Starting Member

14 Posts

Posted - 2013-04-26 : 02:32:59

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 02:38:51

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources