I need to combine this 2 queries. Any ideas? QUERY 1 SELECT date, journalGroup,
ID: 3730138 • Letter: I
Question
I need to combine this 2 queries. Any ideas?
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
QUERY 2
SELECT journalGroup, COUNT(journalGroup) AS 'numParts' FROM mindthegaap.Journals GROUP BY journalGroup
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
Explanation / Answer
Note: I created sample table and checked the below query. It is working fine and giving desired output.
If you have any doubt, please let me know.
SELECT l.*
FROM mindthegaap.Journals l
INNER JOIN(
SELECT journalGroup, count(journalGroup) as 'numParts'
FROM mindthegaap.Journals
GROUP BY journalGroup
) r
ON r.journalGroup = l.journalGroup
ORDER BY l.journalGroup ASC, l.date DESC, l.creditDebit DESC, l.amount DESC
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.