Getting the sum of zero counts

Getting the sum of zero counts

Folks,

I have the following procedure...

INSERT INTO #tempDailyStatistics
SELECT outcome AS [Outcome], COUNT(outcome) AS [Total]
FROM  liveHistory

GROUP BY outcome

INSERT INTO #tempDailyStatistics
SELECT outcome AS [Outcome], COUNT(outcome) AS [Total]
FROM  expiredHistory

GROUP BY outcome

Whick works perfect apart from one simple flaw.  That is if I run the report
for today, I get all the outcomes for today only.  What I want is EVERY
single outcome in the table, even if an outcome isn't related to today.  How
can this be done?

TIA

Getting the sum of zero counts

Take out the WHERE clause?

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

> Folks,

> I have the following procedure...

> INSERT INTO #tempDailyStatistics
>  SELECT outcome AS [Outcome], COUNT(outcome) AS [Total]
>  FROM  liveHistory

>  GROUP BY outcome

>  INSERT INTO #tempDailyStatistics
>  SELECT outcome AS [Outcome], COUNT(outcome) AS [Total]
>  FROM  expiredHistory

>  GROUP BY outcome

> Whick works perfect apart from one simple flaw.  That is if I run the
report
> for today, I get all the outcomes for today only.  What I want is EVERY
> single outcome in the table, even if an outcome isn't related to today.
How
> can this be done?

> TIA

Getting the sum of zero counts

No no no, I need to report on a specific date, so I need the where clause
in.

What I'm saying, for example,  is if on monday I sold 1 apple, 3 orange and
6 pears, then on thursday I need it to say 1 apple, 0 oranges and 3 pears.

> Take out the WHERE clause?

> --
> Aaron Bertrand, SQL Server MVP
> http://www.aspfaq.com/

> > Folks,

> > I have the following procedure...

> > INSERT INTO #tempDailyStatistics
> >  SELECT outcome AS [Outcome], COUNT(outcome) AS [Total]
> >  FROM  liveHistory

> >  GROUP BY outcome

> >  INSERT INTO #tempDailyStatistics
> >  SELECT outcome AS [Outcome], COUNT(outcome) AS [Total]
> >  FROM  expiredHistory

> >  GROUP BY outcome

> > Whick works perfect apart from one simple flaw.  That is if I run the
> report
> > for today, I get all the outcomes for today only.  What I want is EVERY
> > single outcome in the table, even if an outcome isn't related to today.
> How
> > can this be done?

> > TIA

Getting the sum of zero counts

Can you post a repro script with sample data & expected results?

--
- Anith

Getting the sum of zero counts

Well the script I'm using is as follows...

INSERT INTO #tempDailyStatistics
SELECT outcome AS [Outcome], COUNT(outcome) AS [Total]
FROM  liveHistory

GROUP BY outcome

INSERT INTO #tempDailyStatistics
SELECT outcome AS [Outcome], COUNT(outcome) AS [Total]
FROM  expiredHistory

GROUP BY outcome

Some sample data is as follows...

Discontinued 44  Customer Moved Address 5
Engaged 136  Discontinued 78
Fax Machine 5  Engaged 165
No Answer 314  Fax Machine 5
Out Of Order 18  No Answer 481
Uncontactable 3  Out Of Order 30
Wrong Number 34  Uncontactable 1
Call Back 109  Wrong Number 22
Corporate Company 173  Call Back 84
Energy Sale 11  Corporate Company 226
Decision Maker Not Available 621  Energy Sale 21
Information Requested 22  Decision Maker Not Available 835
MegaHertz Customer 3  Information Requested 18
Not Interested 1065  MegaHertz Customer 3
Private Residence 5  Not Interested 1165
Total calls made 2687  Private Residence 9
No contacts 678  Total calls made 3306
Total Sales 11   No contacts 945
Total Sales 2

As you may notice, it's out of line.  This is because the far right column
has "Customer Moved Address" where the left column doesn't.  What I want to
do, is show specifically on the left hand column that "Customer Moved

TIA

Quote:> Can you post a repro script with sample data & expected results?

> --
> - Anith
> (Please respond only to newsgroups)

I need some SQL syntax help... I know I'm missing something obvious!

Is there a simple way to get a subtotal (aggregate sum) on an aggregate sum?
For example, I have a tabe of order line items that includes the quantity
and price of each line item... I can get the subtotal for each line like
this:

select linetotal= sum(quantity * priceeach) from orderlineitems where
orderid = 'D3DD6AA0-2A66-11D4-8BD1-00105AA751F6'

How can I, with a single SQL statement (if that's even possible), get the
total for the entire order (sum of all "linetotals" from the query shown
above)?

Thanks,

Troy Whistman
Digital MD Systems