Adding count column to record matches over multiple fields

[bd528]

Hi all,

Is it possible to add a column to the below to record how many instances of a matching customer, for the same broker occur on the same date :-

SELECT q.QUOTEID, c.CUSTOMER_NAME, b.BROKERAGE_NAME, q.REQUESTED_SSD
FROM ((TBLQUOTESNEW q LEFT JOIN TBLBROKERAGESNEW b ON q.BROKERAGE_ID = b.ID) LEFT JOIN TBLCUSTOMERSNEW c ON q.CUSTOMER_ID = c.CUSTOMERID) LEFT JOIN TBLSUPPLIESNEW s ON q.SUPPLY_ID = s.SUPPLYID
WHERE (((b.BROKERAGE_NAME) Is Not Null) AND ((q.CONTRACT_TYPE)="Verbal") AND ((s.UTILITY)="Electricity"))
ORDER BY b.BROKERAGE_NAME, q.REQUESTED_SSD;

Example output could be

QUOTEID CUSTOMER_NAME BROKERAGE_NAME REQUESTED_SSD XCOUNT
1234    CUST1         BROKER1        01/01/2017    2
5678    CUST1         BROKER1        01/01/2017    2
8666    CUST3         BROKER3        01/01/2017    1

Thanks in advance

 

[plog]

Yes, but not in that query. You would build another one on top of it. Let's call yours 'sub1'. Then you would build this query:

SELECT QUOTEID, CUSTOMER_NAME, BROKERAGE_NAME, REQUESTED_SSD, DCOUNT("[QUOTEID]", "sub1", "[CUSTOMER_NAME]='" & [CUSTOMER_NAME] &"' AND [BROKERAGE_NAME]='" & [BROKERAGE_NAME] & "' AND [REQUESTED_SSD]=#" & [REQUESTED_SSD] & "#") AS XCOUNT
FROM sub1

 

[bd528]

Yes, but not in that query. You would build another one on top of it. Let's call yours 'sub1'. Then you would build this query:

SELECT QUOTEID, CUSTOMER_NAME, BROKERAGE_NAME, REQUESTED_SSD, DCOUNT("[QUOTEID]", "sub1", "[CUSTOMER_NAME]='" & [CUSTOMER_NAME] &"' AND [BROKERAGE_NAME]='" & [BROKERAGE_NAME] & "' AND [REQUESTED_SSD]=#" & [REQUESTED_SSD] & "#") AS XCOUNT
FROM sub1

Thank you for the reply.

That basically does what I want. Out of curiosity, some of the counts are zero. Any idea why?

 

[plog]

No. None should be zero.

Can you provied sample data? Give me data from both sub1 and the query I provided.

 

[arnelgp]

if you have saved your query (say
you saved it as Query1), you can count it
like this:

SELECT Query1.QUOTEID, Query1.CUSTOMER_NAME,
Query1.BROKERAGE_NAME, Query1.REQUESTED_SSD,
(SELECT Count(*) FROM Query1 AS T1
WHERE
T1.CUSTOMER_NAME=Query1.CUSTOMER_NAME AND
T1.BROKERAGE_NAME=Query1.BROKERAGE_NAME)
AS XCOUNT
FROM Query1

***
just remember to replace Query1 with
your saved Query name.

 

[bd528]

No. None should be zero.

Can you provied sample data? Give me data from both sub1 and the query I provided.

Not sure that's going to help as the data is the same from both, just with the count column, but :-

QUOTEID	CUSTOMER_NAME	BROKERAGE_NAME	REQUESTED_SSD
961945	Cust1	        Bro1	        06/10/2017

and

quoteid	customer_name	brokerage_name	requested_ssd	XCOUNT
961945	Cust1	        Bro1	        06/10/2017	0

 

[plog]

My query code returns 1 for XCOUNT when I feed it the initial data in your prior post.