Sagar Galande Sagar Galande - 3 months ago 7
SQL Question

Duplicate Dates in Column Grouped using Group By

I am trying to gate the count of rows having same date from multyple tables using group by, But the resultant rows showing duplicate dates. I have changed date format to show only the date removing time from the date column.

SELECT
userprofiles."date",
COUNT(userprofiles."date") AS NumberofRegistration,
COUNT(CallScheduleTableFB."Date") AS NumberofCalls
FROM userprofiles LEFT JOIN CallScheduleTableFB ON userprofiles."date" = CallScheduleTableFB."Date"
GROUP BY userprofiles."date"
UNION
SELECT
CallScheduleTableFB."Date",
COUNT(userprofiles."date") AS NumberofRegistration,
COUNT(CallScheduleTableFB."Date") AS NumberofCalls
FROM userprofiles RIGHT JOIN CallScheduleTableFB ON userprofiles."date" = CallScheduleTableFB."Date"
GROUP BY CallScheduleTableFB."Date"


EDIT
Following is output

userprofiles.date |NumberofRegistration |NumberofCalls
27/07/2016 |1 |0
28/07/2016 |0 |1
28/07/2016 |0 |1
28/07/2016 |0 |1
28/07/2016 |0 |1
30/07/2016 |0 |1
30/07/2016 |0 |1
03/08/2016 |1 |0
03/08/2016 |1 |0
03/08/2016 |0 |1
03/08/2016 |0 |1

Answer

@Sagar Galande, This is my suggested code:

SELECT
      A.[Date]
    , SUM(A.NumberofRegistration) AS NumberofRegistration
    , SUM(A.NumberofCalls) AS NumberofRegistration
FROM
    (
    SELECT
         userprofiles."date" AS [Date],
         COUNT(userprofiles."date") AS NumberofRegistration,
         COUNT(CallScheduleTableFB."Date") AS NumberofCalls
    FROM  
        userprofiles 
            LEFT JOIN 
        CallScheduleTableFB 
            ON userprofiles."date"  = CallScheduleTableFB."Date"  
    GROUP BY  
        userprofiles."date" 
    UNION
    SELECT
         CallScheduleTableFB."Date" AS [Date],
         COUNT(userprofiles."date") AS NumberofRegistration,
         COUNT(CallScheduleTableFB."Date") AS NumberofCalls
    FROM  
        userprofiles 
            RIGHT JOIN 
        CallScheduleTableFB 
            ON userprofiles."date"  = CallScheduleTableFB."Date"  
    GROUP BY  
        CallScheduleTableFB."Date"
    ) AS A
GROUP BY
    A.[Date]