none
how to count value column (Proce) in sub table and show count number with main table where main.username = sub.username use GROUP BY ? RRS feed

  • Question

  • I have 2 tables :

    CREATE TABLE [dbo].[MAIN](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[username] [nchar](10) NULL,
    	[work] [nchar](10) NULL,
    ) ON [PRIMARY]
    
    CREATE TABLE [dbo].[SUB](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Proce] [varchar](50) NULL,
    	[username] [varchar](50) NULL,
    ) ON [PRIMARY]
    
    SET IDENTITY_INSERT [dbo].[MAIN] ON 
    
    INSERT [dbo].[MAIN] ([ID], [username], [work]) VALUES (1, N'hamza     ', N'A         ')
    INSERT [dbo].[MAIN] ([ID], [username], [work]) VALUES (2, N'hamza     ', N'A         ')
    INSERT [dbo].[MAIN] ([ID], [username], [work]) VALUES (3, N'hider     ', N'B         ')
    INSERT [dbo].[MAIN] ([ID], [username], [work]) VALUES (4, N'alis      ', N'C         ')
    INSERT [dbo].[MAIN] ([ID], [username], [work]) VALUES (5, N'alis      ', N'C         ')
    INSERT [dbo].[MAIN] ([ID], [username], [work]) VALUES (6, N'alis      ', N'C         ')
    INSERT [dbo].[MAIN] ([ID], [username], [work]) VALUES (7, N'alis      ', N'C         ')
    SET IDENTITY_INSERT [dbo].[MAIN] OFF
    SET IDENTITY_INSERT [dbo].[SUB] ON 
    
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (1, N'Search', N'hamza')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (2, N'Search', N'hamza')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (3, N'Update', N'hider')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (4, N'Search', N'alis')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (5, N'Search', N'hider')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (6, N'Update', N'hamza')
    SET IDENTITY_INSERT [dbo].[SUB] OFF
    

    I use query :

    SELECT [username],work ,(select count (*) from [SUB]) as [CountSearchSub],(select count (*) from [SUB]) as [CountUpdateSub]
     FROM [MAIN]
     GROUP BY [username],work

    I want :



    Saturday, September 30, 2017 9:16 PM

Answers

  • SELECT MAIN.[username], MAIN.work,
           isnull(SUM(CASE WHEN SUB.Proce = 'Search' THEN 1 END), 0) AS [CountSearchSub],
           isnull(SUM(CASE WHEN SUB.Proce = 'Update' THEN 1 END), 0) AS [CountUpdateSub]
    FROM   (SELECT DISTINCT username, work FROM MAIN) AS MAIN
    JOIN   SUB ON MAIN.username = SUB.username
    GROUP BY MAIN.[username], MAIN.work


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by srajmuneer Monday, October 2, 2017 7:44 AM
    Saturday, September 30, 2017 10:09 PM
  • CREATE TABLE [dbo].[MAIN](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[username] [nchar](10) NULL,
    	[work] [nchar](10) NULL,
    	[AddressA] [varchar](20) NULL
    ) ON [PRIMARY]
    
    CREATE TABLE [dbo].[SUB](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Proce] [varchar](50) NULL,
    	[username] [varchar](50) NULL,
    ) ON [PRIMARY]
    
    SET IDENTITY_INSERT [dbo].[MAIN] ON 
    
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (1, N'hamza     ', N'A         ','Address Test AA')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (2, N'hamza     ', N'A         ', 'Address Test AB')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (3, N'hider     ', N'B         ','Address Test ABC')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (4, N'alis      ', N'C         ', 'Address Test ABCD')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (5, N'alis      ', N'C         ','Address Test A')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (6, N'alis      ', N'C         ','Address Test')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (7, N'alis      ', N'C         ','Address')
    SET IDENTITY_INSERT [dbo].[MAIN] OFF
    SET IDENTITY_INSERT [dbo].[SUB] ON 
    
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (1, N'Search', N'hamza')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (2, N'Search', N'hamza')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (3, N'Update', N'hider')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (4, N'Search', N'alis')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (5, N'Search', N'hider')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (6, N'Update', N'hamza')
    SET IDENTITY_INSERT [dbo].[SUB] OFF
    
    WITH CTE AS (
    SELECT MAIN.[username], MAIN.work, sum(len(isnull(addressA,''))) [CountCharAddressA]
    FROM MAIN
    GROUP BY MAIN.[username], MAIN.work
    )
    
    SELECT MAIN.[username], MAIN.work, MAIN.[CountCharAddressA] , 
           isnull(SUM(CASE WHEN SUB.Proce = 'Search' THEN 1 END), 0) AS [CountSearchSub],
           isnull(SUM(CASE WHEN SUB.Proce = 'Update' THEN 1 END), 0) AS [CountUpdateSub]
    FROM   (SELECT DISTINCT username, work , [CountCharAddressA]  FROM CTE) AS MAIN
    JOIN   SUB ON MAIN.username = SUB.username
    GROUP BY MAIN.[username], MAIN.work, MAIN.[CountCharAddressA]



    Ousama EL HOR, MCSE|MCSA|MCP. Please mark as answered, If you feel happy with this answer.



    Sunday, October 1, 2017 3:31 PM
  • Hi srajmuneer,

    Or you can just replace the sub select with CTE based on Ousama's query. Please refer:

    SELECT MAIN.[username], MAIN.work, MAIN.[CountCharAddressA] , 
           isnull(SUM(CASE WHEN SUB.Proce = 'Search' THEN 1 END), 0) AS [CountSearchSub],
           isnull(SUM(CASE WHEN SUB.Proce = 'Update' THEN 1 END), 0) AS [CountUpdateSub]
    FROM (SELECT MAIN.[username], MAIN.work, sum(len(isnull(addressA,''))) [CountCharAddressA]
    			FROM MAIN
    			GROUP BY MAIN.[username], MAIN.work) AS MAIN
    JOIN SUB ON MAIN.username = SUB.username
    GROUP BY MAIN.[username], MAIN.work, MAIN.[CountCharAddressA]

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by srajmuneer Monday, October 2, 2017 7:45 AM
    Monday, October 2, 2017 6:13 AM

All replies

  • SELECT MAIN.[username], MAIN.work,
           isnull(SUM(CASE WHEN SUB.Proce = 'Search' THEN 1 END), 0) AS [CountSearchSub],
           isnull(SUM(CASE WHEN SUB.Proce = 'Update' THEN 1 END), 0) AS [CountUpdateSub]
    FROM   (SELECT DISTINCT username, work FROM MAIN) AS MAIN
    JOIN   SUB ON MAIN.username = SUB.username
    GROUP BY MAIN.[username], MAIN.work


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by srajmuneer Monday, October 2, 2017 7:44 AM
    Saturday, September 30, 2017 10:09 PM
  • thank you  Erland  Sommarskog your query very good

    but I have problem when add column (addressA) and count char column Give incorrect results >> why ?>> What is the solution?

    CREATE TABLE [dbo].[MAIN](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[username] [nchar](10) NULL,
    	[work] [nchar](10) NULL,
    	[addressA] [nvarchar](50) NULL
    ) ON [PRIMARY]
    
    GO
    SET IDENTITY_INSERT [dbo].[MAIN] ON 
    
    INSERT [dbo].[MAIN] ([ID], [username], [work], [addressA]) VALUES (1, N'hamza     ', N'A         ', N'street qw')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [addressA]) VALUES (2, N'hamza     ', N'A         ', N'street sd')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [addressA]) VALUES (3, N'hider     ', N'B         ', N'street qw')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [addressA]) VALUES (4, N'alis      ', N'C         ', NULL)
    INSERT [dbo].[MAIN] ([ID], [username], [work], [addressA]) VALUES (5, N'alis      ', N'C         ', NULL)
    INSERT [dbo].[MAIN] ([ID], [username], [work], [addressA]) VALUES (6, N'alis      ', N'C         ', NULL)
    INSERT [dbo].[MAIN] ([ID], [username], [work], [addressA]) VALUES (7, N'alis      ', N'C         ', NULL)
    SET IDENTITY_INSERT [dbo].[MAIN] OFF
    SELECT MAIN.[username], MAIN.work, sum(len(isnull(addressA,''))) as [CountCahrAddressA] , 
           isnull(SUM(CASE WHEN SUB.Proce = 'Search' THEN 1 END), 0) AS [CountSearchSub],
           isnull(SUM(CASE WHEN SUB.Proce = 'Update' THEN 1 END), 0) AS [CountUpdateSub]
    FROM   (SELECT DISTINCT username, work , addressA FROM MAIN) AS MAIN
    JOIN   SUB ON MAIN.username = SUB.username
    GROUP BY MAIN.[username], MAIN.work






    • Edited by srajmuneer Sunday, October 1, 2017 3:29 PM
    Sunday, October 1, 2017 2:12 PM
  • CREATE TABLE [dbo].[MAIN](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[username] [nchar](10) NULL,
    	[work] [nchar](10) NULL,
    	[AddressA] [varchar](20) NULL
    ) ON [PRIMARY]
    
    CREATE TABLE [dbo].[SUB](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Proce] [varchar](50) NULL,
    	[username] [varchar](50) NULL,
    ) ON [PRIMARY]
    
    SET IDENTITY_INSERT [dbo].[MAIN] ON 
    
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (1, N'hamza     ', N'A         ','Address Test AA')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (2, N'hamza     ', N'A         ', 'Address Test AB')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (3, N'hider     ', N'B         ','Address Test ABC')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (4, N'alis      ', N'C         ', 'Address Test ABCD')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (5, N'alis      ', N'C         ','Address Test A')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (6, N'alis      ', N'C         ','Address Test')
    INSERT [dbo].[MAIN] ([ID], [username], [work], [AddressA]) VALUES (7, N'alis      ', N'C         ','Address')
    SET IDENTITY_INSERT [dbo].[MAIN] OFF
    SET IDENTITY_INSERT [dbo].[SUB] ON 
    
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (1, N'Search', N'hamza')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (2, N'Search', N'hamza')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (3, N'Update', N'hider')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (4, N'Search', N'alis')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (5, N'Search', N'hider')
    INSERT [dbo].[SUB] ([ID], [Proce], [username]) VALUES (6, N'Update', N'hamza')
    SET IDENTITY_INSERT [dbo].[SUB] OFF
    
    WITH CTE AS (
    SELECT MAIN.[username], MAIN.work, sum(len(isnull(addressA,''))) [CountCharAddressA]
    FROM MAIN
    GROUP BY MAIN.[username], MAIN.work
    )
    
    SELECT MAIN.[username], MAIN.work, MAIN.[CountCharAddressA] , 
           isnull(SUM(CASE WHEN SUB.Proce = 'Search' THEN 1 END), 0) AS [CountSearchSub],
           isnull(SUM(CASE WHEN SUB.Proce = 'Update' THEN 1 END), 0) AS [CountUpdateSub]
    FROM   (SELECT DISTINCT username, work , [CountCharAddressA]  FROM CTE) AS MAIN
    JOIN   SUB ON MAIN.username = SUB.username
    GROUP BY MAIN.[username], MAIN.work, MAIN.[CountCharAddressA]



    Ousama EL HOR, MCSE|MCSA|MCP. Please mark as answered, If you feel happy with this answer.



    Sunday, October 1, 2017 3:31 PM
  • Hi srajmuneer,

    Or you can just replace the sub select with CTE based on Ousama's query. Please refer:

    SELECT MAIN.[username], MAIN.work, MAIN.[CountCharAddressA] , 
           isnull(SUM(CASE WHEN SUB.Proce = 'Search' THEN 1 END), 0) AS [CountSearchSub],
           isnull(SUM(CASE WHEN SUB.Proce = 'Update' THEN 1 END), 0) AS [CountUpdateSub]
    FROM (SELECT MAIN.[username], MAIN.work, sum(len(isnull(addressA,''))) [CountCharAddressA]
    			FROM MAIN
    			GROUP BY MAIN.[username], MAIN.work) AS MAIN
    JOIN SUB ON MAIN.username = SUB.username
    GROUP BY MAIN.[username], MAIN.work, MAIN.[CountCharAddressA]

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by srajmuneer Monday, October 2, 2017 7:45 AM
    Monday, October 2, 2017 6:13 AM