I am kinda stuck here trying to create a query, this is a simplified version of what I am looking for but should be sufficient to be able to work as a basis for the logic required.
Essentially what is going on, I am going to be running this query for 100s of different customerIDs that are associated with a parent customer. but I need to be able to grab all the documents for that customer and group them by their receiverID. This would then get me the number of documents that the customer has sent to the receiver.
I think the way to do this would be some kind of UNIONED loop that will take the customers ids that have the customer parent ID and use that as the value in the primary loop.
For example here would be an example table that should give a good understanding of requirements
Document Table
DocumentID | CustomerID | receiverID | customerParentID |
---|---|---|---|
1 | 1 | 25 | 8 |
2 | 1 | 25 | 8 |
3 | 1 | 26 | 8 |
4 | 1 | 26 | 8 |
5 | 1 | 27 | 8 |
6 | 4 | 25 | 8 |
7 | 3 | 25 | 8 |
8 | 3 | 26 | 8 |
9 | 2 | 26 | 8 |
10 | 2 | 27 | 8 |
The result of the table should show up like this.
Number of Documents | CustomerID | ReceiverID |
---|---|---|
2 | 1 | 25 |
2 | 1 | 26 |
1 | 1 | 27 |
1 | 4 | 25 |
1 | 3 | 25 |
1 | 3 | 26 |
1 | 2 | 26 |
1 | 2 | 27 |
Basically my thoughts right now are to do the following query, this would correctly group the information but would not work for also separating out the customerIDs.
I think I would have to do a nested select in a loop to make this happen.
SELECT
COUNT(d.DocumentID), customerID, receiverID
FROM
document d
WHERE
d.customerID IN (SELECT
d.customerID
FROM
document
WHERE
customerParentID = 8)
GROUP BY receiverID
The problem with the above query is that it would return the following. which would be incorrect.
Number of Documents | CustomerID | ReceiverID |
---|---|---|
4 | 1 | 25 |
4 | 1 | 26 |
2 | 1 | 27 |
Thank you all for your time and let me know if you have questions that I can answer to help get a solution to this.
The effect functionality that I want is the following but in a loop.
SELECT
COUNT(d.DocumentID), customerID, receiverID
FROM
document d
WHERE
d.customerID = '1'
GROUP BY receiverID
UNION SELECT
COUNT(d.DocumentID), customerID, receiverID
FROM
document d
WHERE
d.customerID = '2'
GROUP BY receiverID
UNION SELECT
COUNT(d.DocumentID), customerID, receiverID
FROM
document d
WHERE
d.customerID = '3'
GROUP BY receiverID
UNION SELECT
COUNT(d.DocumentID), customerID, receiverID
FROM
document d
WHERE
d.customerID = '4'
GROUP BY receiverID
CodePudding user response:
Just add "customerID" inside the GROUP BY
clause and filter the "customerParentID" if necessary.
SELECT COUNT(d.DocumentID),
customerID,
receiverID
FROM document d
WHERE customerParentID = 8
GROUP BY customerID,
receiverID