sum the total from a count query (1 Viewer)

megatronixs

Registered User.
Local time
Today, 12:09
Joined
Aug 17, 2012
Messages
719
Hi all,

I have a query that will give me the results from how many countries come from a criteria.
HK AE 18
HK CN 1
HK TR 1
HK VC 1

I try to get the sum from all inside a separate query that will give me simply: 21

I use the below query but I don't get it :-(

Code:
SELECT COUNT(*), count(SUBQUERY.ORDERING_INST_NAME) from
(
SELECT Scnr1.ORDERING_INST_COUNTRY, Scnr1.BENEFICIARY_INST_COUNTRY, Count(Scnr1.ORDERING_INST_NAME) AS CountOfORDERING_INST_NAME
FROM Scnr1
GROUP BY Scnr1.ORDERING_INST_NAME, Scnr1.ORDERING_INST_COUNTRY, Scnr1.BENEFICIARY_INST_COUNTRY, Scnr1.DEBIT_ACCOUNT_NUMBER
HAVING (((Scnr1.DEBIT_ACCOUNT_NUMBER)="123456789"))
) AS SUBQUERY

Greetings.
 

jleach

Registered User.
Local time
Today, 07:09
Joined
Jan 4, 2012
Messages
308
Try:

Code:
SELECT SUM(a.CountOfORDERING_INST_NAME) AS TotalCount
FROM (
  your existing subquery here
) AS a

(after all, you want the sum of that column, rather than the count of records returned)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:09
Joined
May 7, 2009
Messages
19,175
SELECT Scnr1.ORDERING_INST_COUNTRY,
Scnr1.BENEFICIARY_INST_COUNTRY,
Count(Scnr1.ORDERING_INST_NAME) AS CountOfORDERINT_INST_NAME,
(SELECT Count(*) FROM Scnr1 AS T1 WHERE T1.ORDERING_INST_COUNTRY=Scnr1.ORDERING_INST_COUNTRY) AS TotalCount
FROM Scnr1
GROUP BY Scnr1.ORDERING_INST_NAME, Scnr1.ORDERING_INST_COUNTRY, Scnr1.BENEFICIARY_INST_COUNTRY, Scnr1.DEBIT_ACCOUNT_NUMBER
HAVING (((Scnr1.DEBIT_ACCOUNT_NUMBER)="123456789"))
 

megatronixs

Registered User.
Local time
Today, 12:09
Joined
Aug 17, 2012
Messages
719
hi all, I don't get it to work. Seems the initial query got messed up and does not make any sense now.

I made one of the queries a make table query where I have my results now.
so I get my result as below:
HK AE 18
HK CN 1
HK TR 1
HK VC 1

I called the table: tbl_scnr2_1

Any idea how to make a query to get the sum of 21?

greetings.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:09
Joined
May 7, 2009
Messages
19,175
still no luck? try:

SELECT ORDERING_INST_COUNTRY, SUM(CountOfORDERINT_INST_NAME) AS TotalCount FROM
(SELECT Scnr1.ORDERING_INST_COUNTRY,
Scnr1.BENEFICIARY_INST_COUNTRY,
Count(Scnr1.ORDERING_INST_NAME) AS CountOfORDERINT_INST_NAME,
FROM Scnr1
GROUP BY Scnr1.ORDERING_INST_NAME, Scnr1.ORDERING_INST_COUNTRY, Scnr1.BENEFICIARY_INST_COUNTRY, Scnr1.DEBIT_ACCOUNT_NUMBER
HAVING (((Scnr1.DEBIT_ACCOUNT_NUMBER)="123456789"))) GROUP BY ORDERING_INST_COUNTRY
 

megatronixs

Registered User.
Local time
Today, 12:09
Joined
Aug 17, 2012
Messages
719
Hi, I did not manage to get it work, but I managed to get the query working to get the sum of the count.
Maybe not an elegant solution, but it works :)
 

Users who are viewing this thread

Top Bottom