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.
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 :PHONE111010100I 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 1PHONE(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 TableNameFROM YourSourceTable[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 4Incorrect syntax near '1'.Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'ORDER'. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-25 : 06:04:46
|
--some fixes for above querySELECT 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 CountDistValuesINTO NewTableFROM @tabSELECT * FROM NewTableNOTE: @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 DistValCountfrom (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 MVPhttp://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 MaxColFlgFROM @tab)t GROUP BY PHONE,TotalCount Col will be unique valued column (id field or datetime field)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MaxColFlgFROM @tab)t GROUP BY PHONE,TotalCount Col will be unique valued column (id field or datetime field)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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, PHONEFROM (SELECT *,COUNT(PHONE) OVER () AS TotalCount,CASE WHEN MAX(Col) OVER (PARTITION BY PHONE) = Col THEN 1 ELSE 0 END AS MaxColFlgFROM @tab)t )rGROUP BY PHONE,TotalCount,DistCount ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 ExampleSELECT * 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 MaxColFlgFROM @tab)t GROUP BY PHONE,TotalCount Col will be unique valued column (id field or datetime field)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 CTEAS(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_DistFROM(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_STATEFROM 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.CntFROM (SELECT 1 AS Rn,* FROM CTE) cFULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY PHONE_IN) AS Rn,PHONE_IN,COUNT(*) AS Cnt FROM Table GROUP BY PHONE_IN )pON p.Rn = c.RnFULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY AFFL_IN ) AS Rn,AFFL_IN ,COUNT(*) AS Cnt FROM Table GROUP BY AFFL_IN )aON a.Rn = p.RnFULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY STATE) AS Rn,STATE,COUNT(*) AS Cnt FROM Table GROUP BY STATE )sON s.Rn = a.Rn ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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() OverallCountFROM 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 CTEAS(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_DistFROM(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_STATEFROM 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.CntFROM (SELECT 1 AS Rn,* FROM CTE) cFULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY PHONE_IN) AS Rn,PHONE_IN,COUNT(*) AS Cnt FROM Table GROUP BY PHONE_IN )pON p.Rn = c.RnFULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY AFFL_IN ) AS Rn,AFFL_IN ,COUNT(*) AS Cnt FROM Table GROUP BY AFFL_IN )aON a.Rn = p.RnFULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY STATE) AS Rn,STATE,COUNT(*) AS Cnt FROM Table GROUP BY STATE )sON s.Rn = a.Rn ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 CTEAS(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_DistFROM(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_STATEFROM 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.CntFROM (SELECT 1 AS Rn,* FROM CTE) cFULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY PHONE_IN) AS Rn,PHONE_IN,COUNT(*) AS Cnt FROM Table GROUP BY PHONE_IN )pON p.Rn = c.RnFULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY AFFL_IN ) AS Rn,AFFL_IN ,COUNT(*) AS Cnt FROM Table GROUP BY AFFL_IN )aON a.Rn = p.RnFULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY STATE) AS Rn,STATE,COUNT(*) AS Cnt FROM Table GROUP BY STATE )sON s.Rn = a.Rn ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Next Page
|
|
|
|
|