SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 help with query (count with case statement)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 03/29/2008 :  10:58:40  Show Profile  Reply with Quote
Hi,

I have the following query, that returns the proper count value I am looking for. I would like to modify it a little bit, but can't remember exactly how to do it.

select count(messageFromID) FROM tblMessage WHERE messageFromID = 1000) as OutBoundMessages

Basically now, it returns the "OutBoundMessages" column

I would like it to return "OutboundMessages_unChecked" and "OutboundMessages_checked" as well as "OutboundMessages_total" (I guess I could determine this value by adding the two values in the front end too. I definatley dont want to do a lookup to determine the total )

I determine if the column is "checked" or "unChecked" by a column in
tblMessage

For example

tblMessage.checked = 1 = ("checked")
tblMessage.checked = 0 = ("unChecked")


any help much appreciated..

thanks!
mike123

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/29/2008 :  12:25:59  Show Profile  Reply with Quote
select count(case when checked = 0 then messageFromID else NULL end) as  OutboundMessages_UnChecked,count(case when checked = 1 then messageFromID else NULL end) as  OutboundMessages_checked,
count(messageFromID) as  OutboundMessages_total
FROM tblMessage 
WHERE messageFromID = 1000
Go to Top of Page

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 03/29/2008 :  16:43:28  Show Profile  Reply with Quote
Hi visakh16,

thanks very much. I'm having a little trouble integrating it, please let me post my full query. I simplified it previously to make it easier, but realized I messed up.

How can I get the query you suggested into this statement? I keep getting syntax errors when attempting.

Thank you very much once again!!
mike123

SELECT TOP 1 *,

(select count(messageFrom) FROM tblMessage WHERE messageFrom = MES.messageFrom) as OutBoundMessages

FROM tblSpamReports SR

JOIN tblMessage MES on MES.messageID = SR.offendingID





Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30103 Posts

Posted - 03/29/2008 :  17:51:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT m.messageFrom,
SUM(CASE WHEN m.checked = 1 THEN 1 ELSE 0 END) AS Checked,
SUM(CASE WHEN m.checked = 0 THEN 1 ELSE 0 END) AS Unchecked,
COUNT(*) AS Total
FROM tblSpamReports AS sr
INNER JOIN tblMessage AS m ON m.messageID = sr.offendingID
GROUP BY m.messageFrom
ORDER BY m.messageFrom



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 03/30/2008 :  07:37:44  Show Profile  Reply with Quote
Hi Peso,

A lesson to be learned by me. Always post the full query, I truncated it a bit, attemtping to make it easier for you guys to help me, I shouldn't have done this as it backfired .

What you posted is exactly what I wanted, but I'm having difficulty putting it in the query below, which isn't what I originally posted.

Here below is the full query. If your able to help me out on this its much appreciated. Sorry for making this difficult

PS I have one question

Here we bring back 3 columns

SUM(CASE WHEN m.checked = 1 THEN 1 ELSE 0 END) AS Checked,
SUM(CASE WHEN m.checked = 0 THEN 1 ELSE 0 END) AS Unchecked,
COUNT(*) AS Total

I could alternatively bring back just "checked" and "unchecked" and add them together in the web app. Is getting the count(*) of any significant cost percentage wise relative to the query? It's nice to have, I'm just wondering if SQLServer already knows this value as its gone thru the table already (0,1 are the only possible values for checked on this table)



Thanks very much for any help!!!
mike123


SELECT TOP 1 spamReportID, offendingID, SR.spamTypeID, SRT.spamDescription, dateReported, reportedByUserID, UD.nameOnline as reportedByNameOnline,
MES.messageFrom, UD2.nameOnline as spamFromNameOnline, MES.message, MES.subject, MES.date, MES.checked, ISNULL(MES.IP,'') as FROM_IP, UD2.date as memberSince, dateDiff(dd, UD2.date, getDate()) as membershipDays,

(select count(messageFrom) FROM tblMessage WHERE messageFrom = MES.messageFrom) as OutBoundMail,
(select count(messageFrom) FROM tblMessage WHERE messageFrom = MES.messageFrom and message = MES.message) as OutBoundMail_Duplicates,
(select count(spamReportID) FROM tblSpamReports SR2 JOIN tblMessage MES2 ON SR2.offendingID = MES2.messageID WHERE MES2.messageFrom = MES.messageFrom) as totalSpamComplaints,

--OTHER TABLES
(select count(commentFromID) FROM tblComment WHERE commentFromID = MES.messageFrom) as OutBoundComments,
(select count(commentFromID) FROM tblPhotoComments WHERE commentFromID = MES.messageFrom) as OutBoundPhotoComments

FROM tblSpamReports SR

JOIN tblUserDetails UD ON UD.userID = SR.reportedByUserID
JOIN tblSpamReports_Types SRT ON SR.spamTypeID = SRT.spamTypeID
JOIN tblMessage MES on MES.messageID = SR.offendingID
JOIN tblUserDetails UD2 ON UD2.userID = MES.messageFrom






Go to Top of Page

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 04/08/2008 :  17:06:20  Show Profile  Reply with Quote
bump.. still a bit stuck on this one... any help much appreciated !

thanks again :D
mike123
Go to Top of Page

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 04/08/2008 :  19:22:09  Show Profile  Reply with Quote
select
DRIVEDTABLE.TransDate AS [Trans Date],
ISNULL(DRIVEDTABLE.DepositSum,0) AS [total deposits],
ISNULL(DRIVEDTABLE.WithdrawSum,0)AS [total withdrawls],
ISNULL(DRIVEDTABLE.DepositSum,0)-ISNULL(DRIVEDTABLE.WithdrawSum,0)AS Closingbalance FROM (
SELECT TransDate,
SUM( CASE WHEN TransName='deposit' then
Amount
end ) as DepositSum,
SUM( CASE WHEN TransName='Withdraw' then
Amount
END ) as [Closing balance]

from dbo.DepositWithdraw
group by TransDate)AS DRIVEDTABLE
you can use this an example
drived table are very nice and handy to get these kind of works to be done
Go to Top of Page

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 04/09/2008 :  05:53:16  Show Profile  Reply with Quote
quote:
Originally posted by QAZAFI

select
DRIVEDTABLE.TransDate AS [Trans Date],
ISNULL(DRIVEDTABLE.DepositSum,0) AS [total deposits],
ISNULL(DRIVEDTABLE.WithdrawSum,0)AS [total withdrawls],
ISNULL(DRIVEDTABLE.DepositSum,0)-ISNULL(DRIVEDTABLE.WithdrawSum,0)AS Closingbalance FROM (
SELECT TransDate,
SUM( CASE WHEN TransName='deposit' then
Amount
end ) as DepositSum,
SUM( CASE WHEN TransName='Withdraw' then
Amount
END ) as [Closing balance]

from dbo.DepositWithdraw
group by TransDate)AS DRIVEDTABLE
you can use this an example
drived table are very nice and handy to get these kind of works to be done



hmm not sure if this is posted in the wrong thread? or if I should be able to figure it out from your sample code ?

Thanks again, :)
mike123
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000