Get Count(*) from multiple tables using SQL

Question

I have two tables Registration_Table and ApplicationStatus_Table. I have taken one column only from Registration_Table for understanding purpose. In ApplicationStatus_Table based on dates of Registrationdate and FinalSubmissiondate check the status.

Registration_Table

 Registrationdate
 -----------------------
 2016-12-22 12:52:48.493
 2016-12-21 15:05:24.533

ApplicationStatus_Table

FinalSubmissiondate              InnovationStatus
-------------------------------------------------
2016-12-22 12:52:48.493          Completed
2016-12-21 15:05:24.533           11

I need result like below.

 Registrationdate    RegistrationsCount   InnovationsStatus
 ----------------------------------------------------------
 2016-12-22               1                1
 2016-12-21               1                0

I need result on particular date how many Registrations and InnovationsStatus. If InnovationStatus completed means 1 and others all 0.

As I mention in question using sql server. @a_horse_with_no_name — MMK
– MMK, Commented Dec 27, 2016 at 8:29
The question says "using SQL" - SQL is a query language, not a specific DBMS product — user330315
– user330315, Commented Dec 27, 2016 at 8:30
Which one? MySQL, MSSQL, postgres, oracle, etc? SQL is the language. #rtfm — Koen Hollander
– Koen Hollander, Commented Dec 27, 2016 at 8:30
I need sql query for display counts like that above using sql server 2008. @a_horse_with_no_name — MMK
– MMK, Commented Dec 27, 2016 at 8:31

Parfait · Accepted Answer · 2016-12-27 08:51:02Z

2

Consider an aggregate query with a full count and conditional count. Below also extracts the time part from datetime value with zero return in conditional aggregate:

SELECT CONVERT(date, r.Registrationdate) As [Date], Count(*) As RegistrationsCount,
       SUM(CASE WHEN a.Status = 'Completed' THEN 1 ELSE 0 END) As InnovationsStatus
FROM Registration_Table r
INNER JOIN ApplicationStatus_Table a
ON r.Registrationdate = a.FinalSubmissiondate
GROUP BY CONVERT(date, r.Registrationdate)

answered Dec 27, 2016 at 8:51

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jibin Balachandran Over a year ago

@MMK Maybe you have a null column in some of the records. Try count(1) as RegistrationsCount

Jibin Balachandran Over a year ago

@MMK With the given schema in the question,this should work. +1 from my side.

Parfait Over a year ago

@MMK - Please clarify as I do not understand your comment.

MMK Over a year ago

FinalSubmissiondate have some null date but I added count(1) as RegistrationsCount then also not showing. @Parfait

MMK Over a year ago

I added like data conversion then working fine ON CONVERT(date, r.Registrationdate) = CONVERT(date, a.FinalSubmissiondate) but count showing rong, there is 2 registration only showing 6 regisstraiton count Date RegistrationsCount InnovationsStatus 2016-12-06 6 6

|

Jibin Balachandran · Accepted Answer · 2016-12-27 10:14:56Z

0

You can do something like:

SELECT  CAST(r.Registrationdate AS DATE) AS as Registrationdate,
        COUNT(Registrationdate) OVER (PARTITION BY Registrationdate) AS RegistrationsCount,
        COUNT(CASE WHEN  a.InnovationStatus='Completed' THEN 1 ELSE NULL END) as InnovationsStatus
FROM Registration_Table   r
INNER JOIN ApplicationStatus_Table  a
ON a.FinalSubmissiondate=r.Registrationdate
GROUP BY r.Registrationdate
ORDER BY r.Registrationdate DESC

Test Data:

create table #Registration_Table (
 Registrationdate datetime
)

insert into #Registration_Table
values('2016-12-22 12:52:48.493'),('2016-12-21 15:05:24.533')

create table #ApplicationStatus_Table(
 FinalSubmissiondate datetime,
 InnovationStatus varchar(20)
) 

insert into #ApplicationStatus_Table
values ('2016-12-22 12:52:48.493','Completed'),('2016-12-21 15:05:24.533','11')



SELECT  CAST(r.Registrationdate AS DATE),
        COUNT(Registrationdate) OVER (PARTITION BY Registrationdate) AS RegistrationsCount,
        COUNT(CASE WHEN  a.InnovationStatus='Completed' THEN 1 ELSE NULL END) as InnovationsStatus
FROM #Registration_Table   r
INNER JOIN #ApplicationStatus_Table  a
ON a.FinalSubmissiondate=r.Registrationdate
GROUP BY r.Registrationdate
ORDER BY r.Registrationdate DESC

Result:

  Registrationdate       RegistrationsCount InnovationsStatus
     2016-12-22                 1                1
     2016-12-21                 1                0

edited Dec 27, 2016 at 10:14

answered Dec 27, 2016 at 8:43

Jibin Balachandran

3,4491 gold badge27 silver badges39 bronze badges

4 Comments

MMK Over a year ago

Getting error: Incorrect syntax near 'Registrationdate'. @Jibin Balachandran

Jibin Balachandran Over a year ago

@MMK I have updated my answer. Please do check and let me know if you have any issue.

MMK Over a year ago

Date format should like that 2016-12-22. @Jibin Balachandran

Jibin Balachandran Over a year ago

@MMK Ya, you will get it it like that only since we are casting datetime as date.

Vinoth Raj · Accepted Answer · 2016-12-27 11:12:02Z

0

         *****Using this query*****

 select t1.[Registrationdate],
 COUNT(t1.[Registrationdate]) as [RegistrationsCount],
 max(case when t2.[InnovationStatus]='completed' then 1 else 0 end) as [InnovationStatus]
 from [#yourtable1] t1
 Join [#youttable2] t2 on
 t1.[Registrationdate]=t2.[FinalSubmissiondate]
 Group by t1.[Registrationdate] Order by t1.[Registraiondate] Desc;

edited Dec 27, 2016 at 11:12

answered Dec 27, 2016 at 8:57

Vinoth Raj

3061 silver badge14 bronze badges

Collectives™ on Stack Overflow

Get Count(*) from multiple tables using SQL

3 Answers 3

6 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related