I have two queries that I need combined into one. The first query is the one cur
ID: 3730086 • Letter: I
Question
I have two queries that I need combined into one.
The first query is the one currently used to populate the Journals table.
QUERY 1
SELECT date, journalGroup, referenceId, accountName, creditDebit, amount, status, filename, comments FROM mindthegaap.Journals ORDER BY journalGroup ASC, date DESC, creditDebit DESC, amount DESC
Returns:
This works fine except based on how I have things laid out, I can’t include the comments for a journal entry unless I know how many “parts” there are to a journal entry. Number of “parts” is the number of credits + debits for any given transaction. For example, In the table above, the first transaction has 1 debit and 1 credit. This sums to 2 “parts”. A journal entry with 4 debits and 3 credits would have 7 parts.
The following SQL query returns the number of parts for each journalGroup:
QUERY 2
SELECT journalGroup, COUNT(journalGroup) AS 'numParts' FROM mindthegaap.Journals GROUP BY journalGroup
Returns:
Note: journalGroup is the transaction group #. So for the first transaction, the journalGroup number would be 1 regardless of how many “parts” it was made of. The first journal entry made up of 1 debit and 1 credits, each one of those is assigned a journalGroup of 1, as you can see in the journalGroup column. The next journal entry has 3 debits and 1 credit, meaning it has 4 “parts” and each part belongs to the journalGroup 2, as you can see all of them have a “2” in their rows under the journalGroup column.
What I need is for Query 1 to have one more column added to it. That column will be the values returned by Query 2. The query can map them by using the journalGroup numbers.
So it needs to return the following:
referenceId
accountName
creditDebit
status
filename
comments
numParts
2018-03-14
1
C1
Cash
debit
2500
Approved
1238.pdf
This is the description for group 1 comments
2
2018-03-14
1
R1
Accounts Receivable
credit
2500
Approved
1238.pdf
This is the description for group 1 comments
2
2018-03-14
2
O1
Other Cash
debit
750
Approved
group2.pdf
Group 2's comments these are
4
2018-03-14
2
P1
Petty Cash
debit
500
Approved
group2.pdf
Group 2's comments these are
4
2018-03-14
2
C1
Cash
debit
500
Approved
group2.pdf
Group 2's comments these are
4
2018-03-14
2
S1
Office Supplies
credit
1750
Approved
group2.pdf
Group 2's comments these are
4
2018-03-15
3
C1
Cash
debit
700
Pending
Testing Pending and no file uploaded
2
2018-03-15
3
R1
Accounts Receivable
credit
700
Pending
Testing Pending and no file uploaded
5
date
journalGroup 1
referenceId
accountName
creditDebit
amount
status
filename
comments
numParts
2018-03-14
1
C1
Cash
debit
2500
Approved
1238.pdf
This is the description for group 1 comments
2
2018-03-14
1
R1
Accounts Receivable
credit
2500
Approved
1238.pdf
This is the description for group 1 comments
2
2018-03-14
2
O1
Other Cash
debit
750
Approved
group2.pdf
Group 2's comments these are
4
2018-03-14
2
P1
Petty Cash
debit
500
Approved
group2.pdf
Group 2's comments these are
4
2018-03-14
2
C1
Cash
debit
500
Approved
group2.pdf
Group 2's comments these are
4
2018-03-14
2
S1
Office Supplies
credit
1750
Approved
group2.pdf
Group 2's comments these are
4
2018-03-15
3
C1
Cash
debit
700
Pending
Testing Pending and no file uploaded
2
2018-03-15
3
R1
Accounts Receivable
credit
700
Pending
Testing Pending and no file uploaded
5
date2 journalGroup 1 reference!d accountName 2018-03-14 2018-03-14 2018-03-14 2018-03-14 2018-03-14 2018-03-14 2018-03-15 2018-03-15 creditDebit3 amount4 status filename comments 1 C1 1 R1 2 01 2 P1 2 C1 2 S1 3 C1 3 R1 2500 2500 750 500 500 1750 Approved 1238.pdf Approved 1238.pdf Approved group2.pdf Group 2's comments these are This is the description for group 1 comments This is the description for group 1 comments Cash Accounts Receivable credit Other Cash Petty Cash Cash Office Supplies Cash Accounts Receivable credit debit debit debit debit credit debit A pproved group2.pdf Group 2's comments these are Approved group2.pdf Group 2's comments these are Approved group2.pdf Group 2's comments these are 700 Pending 700 Pending Testing Pending and no file uploaded Testing Pending and no file uploadedExplanation / Answer
SELECT a.*, b.num
FROM Journal a
inner join
(SELECT journalgroup, count(journalgroup) as 'num'
FROM Jour group by journalgroup
)b
on b.journalgroup=a.journalgroup order by a.date