January 25, 2012 at 10:30 am
[font="Courier New"]Given a table of transaction consisting of the following columns:
CardType nvarchar(50)
Completed bit
I'm looking for SQL statement which would present detail line to include:
o Each unique CardType and
o A count of which ones had Completed = TRUE and
o A final column containing the SUM of all the Completed count
Note, the Total value will be the same for each detail line because its value is the SUM of all the counts in the Completed column.
Card Type Completed Total
--------------------------- --------- -----
ATM_Issue.CRD 15 600
ATM_RePIN.CRD 3 600
Debit_Business_Issue.CRD 10 600
Debit_Business_RePIN.CRD 10 600
Debit_Issue.CRD 355 600
Debit_RePIN.CRD 41 600
NE_ATM_Issue.CRD 20 600
NE_Debit_Business_Issue.CRD 30 600
NE_Debit_Business_RePIN.CRD 16 600
NE_Debit_Issue.CRD 40 600
Pinnacle_Debit_Issue.CRD 60 600[/font]
January 25, 2012 at 11:03 am
Hi and welcome to SSC. In order for somebody to offer much help we need some help from you first. If you can post ddl (create table statements), sample data (insert statements) and desired output based on your sample data it will make sure we are all on the same page. Take a look at the first link in my signature for best practices on posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2012 at 11:38 am
declare @MyTable table (CardType varchar(50), Completed bit)
insert into @MyTable
VALUES
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 0),
('Debit_Business_RePIN.CRD', 1),
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 1),
('Debit_Business_RePIN.CRD', 0),
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 0),
('Debit_Business_RePIN.CRD', 1),
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 1),
('Debit_Business_RePIN.CRD', 0),
('ATM_Issue.CRD', 0),
('ATM_RePIN.CRD' ,1),
('Debit_Business_Issue.CRD', 1),
('Debit_Business_RePIN.CRD', 0),
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 1),
('Debit_Business_RePIN.CRD', 0)
select CardType, SUM(Convert(int,Completed)) as Completed, COUNT(*) as Total
from @MyTable
group by CardType
January 25, 2012 at 12:12 pm
Pretty close, but the "Total" column is incorrectly valued with "6" versus the expected "12" (5 + 1 + 4 + 2).
Any additional ideas?
CardType Completed Total
-------------------------------------------------- ----------- -----------
ATM_Issue.CRD 5 6
ATM_RePIN.CRD 1 6
Debit_Business_Issue.CRD 4 6
Debit_Business_RePIN.CRD 2 6
January 25, 2012 at 12:14 pm
raisleger (1/25/2012)
Pretty close, but the "Total" column is incorrectly valued with "6" versus the expected "12" (5 + 1 + 4 + 2).Any additional ideas?
CardType Completed Total
-------------------------------------------------- ----------- -----------
ATM_Issue.CRD 5 6
ATM_RePIN.CRD 1 6
Debit_Business_Issue.CRD 4 6
Debit_Business_RePIN.CRD 2 6
What you are describing is a running total. Jeff wrote a great article on this topic. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2012 at 12:28 pm
how about this...
DECLARE @mytable AS TABLE(CardType nvarchar(50),Completed BIT)
insert into @MyTable
VALUES
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 0),
('Debit_Business_RePIN.CRD', 1),
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 1),
('Debit_Business_RePIN.CRD', 0),
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 0),
('Debit_Business_RePIN.CRD', 1),
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 1),
('Debit_Business_RePIN.CRD', 0),
('ATM_Issue.CRD', 0),
('ATM_RePIN.CRD' ,1),
('Debit_Business_Issue.CRD', 1),
('Debit_Business_RePIN.CRD', 0),
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 1),
('Debit_Business_RePIN.CRD', 0)
--SELECT * FROM @mytable
SELECT cardtype,COUNT(completed) AS cardtypecount,(SELECT COUNT(*) FROM @mytable WHERE completed = 1)
FROM @mytable
WHERE completed = 1
GROUP BY cardtype
January 25, 2012 at 1:18 pm
Yes that produced the desired results...Thanks.
January 25, 2012 at 10:16 pm
Sean Lange (1/25/2012)
raisleger (1/25/2012)
Pretty close, but the "Total" column is incorrectly valued with "6" versus the expected "12" (5 + 1 + 4 + 2).Any additional ideas?
CardType Completed Total
-------------------------------------------------- ----------- -----------
ATM_Issue.CRD 5 6
ATM_RePIN.CRD 1 6
Debit_Business_Issue.CRD 4 6
Debit_Business_RePIN.CRD 2 6
What you are describing is a running total. Jeff wrote a great article on this topic. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
Nope... not a running total in this case. It looks like it because of the low number of rows but the total doesn't increment on each row. It's a simple sum by group on each row.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2012 at 10:30 pm
DECLARE @Example AS TABLE
(
CardType nvarchar(50) NOT NULL,
Completed bit NOT NULL
);
INSERT @Example
(CardType, Completed)
VALUES
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 0),
('Debit_Business_RePIN.CRD', 1),
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 1),
('Debit_Business_RePIN.CRD', 0),
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 0),
('Debit_Business_RePIN.CRD', 1),
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 1),
('Debit_Business_RePIN.CRD', 0),
('ATM_Issue.CRD', 0),
('ATM_RePIN.CRD' ,1),
('Debit_Business_Issue.CRD', 1),
('Debit_Business_RePIN.CRD', 0),
('ATM_Issue.CRD', 1),
('ATM_RePIN.CRD' ,0),
('Debit_Business_Issue.CRD', 1),
('Debit_Business_RePIN.CRD', 0);
SELECT
e.CardType,
Completed = COUNT_BIG(*),
Total = SUM(COUNT_BIG(*)) OVER ()
FROM @Example AS e
WHERE
e.Completed = 1
GROUP BY
e.CardType
ORDER BY
e.CardType;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply