Multiple Join with the same table 1

[EchoAlertcom]

Hello,

I have to make a report that only pulls from 1 table. But I need several different ways of looking at the same data.

How do I run summaries of different counts from the same table?

Related columns include of dbo.Leads:

Phone, Signupdate

Here are 4 simple queries I need to join into one query, could someone get me started please?

Select Count(*) as GrossLeadsCount, SignupDate From dbo.Leads Group By SignupDate

Select Count(*) as UniqueLeadCount, SignupDate From dbo.Leads Where UniqueLead = 1 Group By SignupDate

Select Count(*) as ONetMatchCount, SignupDate From dbo.Leads Where OnetMatch = 1 Group By SignupDate

Select Count(*) as UniqueONetCount, SignupDate From dbo.Leads Where UniqueLead = 1 And ONetMatch = 1 Group By SignupDate

I would like the recordset to resemble:

Date:     Gross:  Unique:  ONetMatch: UniqueONet:
08-13-06  399     201      150        134
08-14-06  543     324      175        142

I am stuck and don't know where to start...

Thanks,
Steve

 

[gmmastros]

Thinking about this... Count basically adds 1 for each record. Using this in conjunction with a case/when construct (using sum instead of count), leads to this...

Select SignupDate,
       Count(*) as GrossLeadsCount, 
       Case When UniqueLead = 1 
            Then 1 
            Else 0 
            End As UniqueLeadCount 
       Case When OnetMatch = 1  
            Then 1 
            Else 0 
            End As ONetMatchCount
       Case When UniqueLead = 1 And ONetMatch = 1 
            Then 1 
            Else 0 
            End As UniqueONetCount
From   dbo.Leads 
Group By SignupDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Sorry. I forgot to include the SUM function.

Select SignupDate,
       Count(*) as GrossLeadsCount, 
       [!]Sum([/!]Case When UniqueLead = 1 
                Then 1 
                Else 0 
                End[!])[/!] As UniqueLeadCount 
       Sum(Case When OnetMatch = 1  
                Then 1 
                Else 0 
                End) As ONetMatchCount
       Sum(Case When UniqueLead = 1 And ONetMatch = 1 
                Then 1 
                Else 0 
                End) As UniqueONetCount
From   dbo.Leads 
Group By SignupDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[EchoAlertcom]

I have to say I would have never come up with that.

It looks great. I am getting an error when i run it in query analyzer that is probably trivial but I can't see what's wrong..

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'Sum'.

Thanks again for your help today.

Warmest Regards,
Steve

 

[gmmastros]

Commas to seperate the fields. Again... I'm sorry.

Select SignupDate,
       Count(*) as GrossLeadsCount, 
       Sum(Case When UniqueLead = 1 
                Then 1 
                Else 0 
                End) As UniqueLeadCount[!],[/!]
       Sum(Case When OnetMatch = 1  
                Then 1 
                Else 0 
                End) As ONetMatchCount[!],[/!]
       Sum(Case When UniqueLead = 1 And ONetMatch = 1 
                Then 1 
                Else 0 
                End) As UniqueONetCount
From   dbo.Leads 
Group By SignupDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

You just need to add a comma after each AS columnname statment.

Select SignupDate,
       Count(*) as GrossLeadsCount, 
       Sum(Case When UniqueLead = 1 
                Then 1 
                Else 0 
                End) As UniqueLeadCount[red],[/red]
       Sum(Case When OnetMatch = 1  
                Then 1 
                Else 0 
                End) As ONetMatchCount[red],[/red]
       Sum(Case When UniqueLead = 1 And ONetMatch = 1 
                Then 1 
                Else 0 
                End) As UniqueONetCount
From   dbo.Leads 
Group By SignupDate

 

[EchoAlertcom]

Awesome!!!

It works perfect. This actually will solve another issue I've been having with another query. I'll be able to apply what I've learned here to the other problem.


Thanks again.
Steve