counting multiple values from the same column and grouping by a another column

This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.

TheTable
Order# Customer Status

STATUS has valid values of PROCESSED and INPROGRESS

The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.

I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?

Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customeryou can write a stored proc and use output parameters to return the values..and in your stored proc have multiple sql stmts to get the diff counts..

hth|||This might work for you, there is probably a better way, this can become very expensive with the 2 sub queries if there is a lot of data

 SELECT Customer,
 (
 SELECT COUNT(*)
 FROM TheTable
 WHERE Status = N'Shipped'
 ) AS Shipped,
 (
 SELECT COUNT(*)
 FROM TheTable
 WHERE Status = N'SomeOtherStatus'
 ) AS SomeOtherStatus
 FROM TheTable
 ORDER BY Customer

CountDistinct in a Matrix Report

I have say 10 companies, each company has 2 activities against it. I build my
sql query and it contains the companyid and the activityid against each
record (20 records). I set up a matrix report to break the activities out by
month (created date) and activity type by adding Fields!ActivityID to the
detail section and this works fine. I then want another matrix underneath
that reports how many companies make up the first figure. I use
Countdistinct(Fields!CompanyID) in the detail section and add in subototals.
This report doesn't turn out as I want, the figures in the detail bit are
the same as the first report (they shoild be less), though the subtotal
reports correctly. Anyone know what I'm doing wrong.
Many ThanksSorry Ignore this, I'm being very silly!!
"Paul Whittaker" wrote:
> I have say 10 companies, each company has 2 activities against it. I build my
> sql query and it contains the companyid and the activityid against each
> record (20 records). I set up a matrix report to break the activities out by
> month (created date) and activity type by adding Fields!ActivityID to the
> detail section and this works fine. I then want another matrix underneath
> that reports how many companies make up the first figure. I use
> Countdistinct(Fields!CompanyID) in the detail section and add in subototals.
> This report doesn't turn out as I want, the figures in the detail bit are
> the same as the first report (they shoild be less), though the subtotal
> reports correctly. Anyone know what I'm doing wrong.
> Many Thanks