August 30, 2009 at 2:37 am
Dear all,
I have the following table and data.
CREATE TABLE TMP (
POST_DATDATETIME,
RETL_KY_FIID VARCHAR(5),
RETL_TERM_IDVARCHAR(10),
AUTH_TYPCHAR(4),
AMT_1DECIMAL(10,3)
)
INSERT TMP
SELECT '20090825', 'CCC', '04226348', '0210', 250.00
UNION ALL
SELECT '20090825', 'CCC', '04226348', '0420', 250.00
UNION ALL
SELECT '20090825', 'CCC', '12602916', '0210', 560.00
UNION ALL
SELECT '20090825', 'CCC', '12602916', '0420', 560.00
UNION ALL
SELECT '20090825', 'BBBB', '12603745', '0210', 128.25
UNION ALL
SELECT '20090825', 'BBBB', '12603745', '0420', 128.25
UNION ALL
SELECT '20090825', 'KKK', '12607413', '0210', 2.10
UNION ALL
SELECT '20090825', 'KKK', '12607413', '0420', 2.10
UNION ALL
SELECT '20090825', 'CCC', '05402710', '0210', 10.00
UNION ALL
SELECT '20090825', 'CCC', '05402711', '0210', 10.00
UNION ALL
SELECT '20090825', 'CCC', '04500518', '0210', 1040.00
UNION ALL
SELECT '20090825', 'CCC', '04500519', '0210', 1040.00
UNION ALL
SELECT '20090825', 'CCC', '05400265', '0210', 1000.00
UNION ALL
SELECT '20090825', 'CCC', '05400267', '0210', 1000.00
UNION ALL
SELECT '20090825', 'NNN', '04008000', '0210', 4.80
What i need is to find total transactions for a day, total trasactions with AUTY_TYP='210', total trasactions with AUTY_TYP='420' in single row group by RETL_KY_FIID
RETL_KY_FIID Total 210 420
------------ ----------- --- ---
BBBB 2 ?? ??
CCC 10
KKK 2
NNN 1
Please help me with this
August 30, 2009 at 5:43 am
Assuming that by total you ment the sum of the amt_1 columns, you can do it with the combination of sum and case statement. Here is an example that is based on your script:
select POST_DAT, RETL_KY_FIID, SUM(AMT_1) as TotalTransaction,
SUM(case WHEN AUTH_TYP = '0210' THEN amt_1 ELSE 0 END) as SUM0210,
SUM(case WHEN AUTH_TYP = '0420' THEN amt_1 ELSE 0 END) as SUM0420
from tmp
group by POST_DAT, RETL_KY_FIID
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 30, 2009 at 6:45 am
unsure of exactly what you require as expected output, here is a variation of Adi's code:
select POST_DAT, RETL_KY_FIID, COUNT(AMT_1) AS TOTALTRANS,
SUM(case WHEN AUTH_TYP = '0210' THEN amt_1 ELSE 0 END) as SUM0210,
SUM(case WHEN AUTH_TYP = '0420' THEN amt_1 ELSE 0 END) as SUM0420
from tmp
group by POST_DAT, RETL_KY_FIID
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 30, 2009 at 11:13 am
Thanks Adi and gah. I wanted to have total count of transactions and with 210 count, 420 count. This is the expected results. thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply