Complex Query returning multiple amounts from 1 field 1

[shortstop]

I need to write a query returning the count of a field and sum based on the value of another field in the same table.

The table contains the fields:
Salesperson
Amount
Status

I would like to show for each salesperson:
The count of items for status = 'C'
The sum amount for status = 'C'
The count of items for status = 'R'
The sum amount for status = 'R'

Please help if you can, thank you...

 

[Tim1]

Hiya,

The query that you need is:

SELECT salesperson,
COUNT(amount),
SUM(amount)
FROM table
WHERE status = 'C'
GROUP BY salesperson

SELECT salesperson,
COUNT(amount),
SUM(amount)
FROM table
WHERE status = 'R'
GROUP BY salesperson

Tim

 

[Tim1]

sorry, hit the post button by accident.

You could also do:

SELECT salesperson,
status,
COUNT(amount),
SUM(amount)
FROM table
WHERE status IN ('C','R')
GROUP BY salesperson,
status

if you want all your results from a single table

 

[shortstop]

Tim: Thanks for your reply, Is there any way to have this return the fields in 1 record, i.e.:

SALESPERSON (Count status C) (Total status C) (Count status R) (Total status R)

 

[simco]

The SQL statement is going to look like: SELECT salesperson, count(status) as [new_field_name1], sum(amount) as [new_field_name2] FROM [table_name] GROUP BY salesperson HAVING [new_field_name1]='C' and... (any conditions that you want to add)

I hope i understood you right and this is what you want to do.

 

[tlbroadbent]

There are a number of ways to return one line result sets. Here is one.

Create view vSummary As

SELECT Count(Salesperson) AS Cnt, Sum(Amount) AS Amt, Status
FROM Sales
GROUP BY Status
go

SELECT c.C_Cnt, c.C_Amt, r.R_Cnt, r.R_Amt
FROM vSummary AS c, vSummary AS r
WHERE c.Status='C' AND r.Status='R'
Go
Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm