COUNT(ing) number of customers and numers of contracts 2

[karlomutschler]

Hi,

One customer might have a numer of contracts for a certain tariff and rebate modell

The following query provides the following data

SELECT DISTINCT COUNT(customer_id)
,COUNT(contract_id)
,tariff
,rebate_model
FROM contract
WHERE (select criteria)
GROUP BY tariff, rebate_model
ORDER BY tariff ;

CustID ContrID Tariff Rebate_modell
4 4 1+ Fixed 04
203 203 1+ professional/int./profi talk
18 18 1+ Fixed 03
2 2 1+ Fixed 25
1 1 1+ Fixed 11
22 22 1+ business 24

but it should look like this:

CustID ContrID Tariff Rebate_modell
1 4 1+ Fixed 04
80 203 1+ professional/int./profi talk
10 18 1+ Fixed 03
1 2 1+ Fixed 25
1 1 1+ Fixed 11
12 22 1+ business 24

TIA and kind regards
Karlo

 

[tlbroadbent]

This works in some RDMS.

SELECT
COUNT(DISTINCT customer_id),
COUNT(DISTINCT contract_id),
tariff,
rebate_model
FROM contract
WHERE (select criteria)
GROUP BY tariff, rebate_model
ORDER BY tariff; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[carp]

A couple of things -

Try
SELECT count(DISTINCT customer_id), count(contract_id),...
FROM .....
WHERE .....
GROUP BY .....;

Also, your code may be more efficient if you leave off the ORDER BY clause.
Some RDBMSs, like Oracle, will sort the data in the order of the GROUP BY clause.
If this is the case with your RDBMS, then the data is already sorted by TARIFF. A second sort does nothing but burn cycles.

 

[tlbroadbent]

I agree with carp about the Order By clause. Eliminate if you can. However, SQL Server 7 and 2000 do not order the result set when using a Group By clause. Previous versions did. I believe the current version behavior is the ANSI standard. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[karlomutschler]

Hi Terry and Carp

Terry, your suggestion

SELECT
COUNT(DISTINCT customer_id),
COUNT(DISTINCT contract_id),
tariff,
....

did not work.

but everything was helpful to find the solution:

SELECT count(DISTINCT customer_id), count(contract_id),...
FROM .....

Thanks a lot and kindest regards
Enjoy the weekend.
Karlo