Solved

SQL SUM

Posted on 2010-09-01
6
412 Views
Last Modified: 2012-05-10
Hi  Experts ,

In this attached code , i want am getting desired output, but i want sum as well . How do i go about it.
SELECT count(*), (
case
when PICSTATUS = '11' then 'Transfer Rejected'
when picstatus = '12' then 'Transfer Cancelled'
when picstatus = '20' then 'New Sale'
when picstatus = '70' then 'Pricing Accepted'
when picstatus = '72' then 'Sale Confirmed'
when picstatus = '80' then 'Ready for Transfer'
when picstatus = '90' then 'Transfer in progress'
end
) as STATUS FROM PROCONSUMER WHERE PICSTATUS NOT IN (100,10) GROUP BY PICSTATUS "

Open in new window

0
Comment
Question by:santoshmotwani
  • 4
  • 2
6 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33582699
What column do you need to SUM?

Also, this is in both MySQL and SQL Server: which one are you using ?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33582706
Anyway, for whichever column:

(note you can simplify case to -- case PICSTATUS when '11' then 'Transfer Rejected' when '12' ...)


SELECT count(*), sum(some_column), (

case

when PICSTATUS = '11' then 'Transfer Rejected'

when picstatus = '12' then 'Transfer Cancelled'

when picstatus = '20' then 'New Sale'

when picstatus = '70' then 'Pricing Accepted'

when picstatus = '72' then 'Sale Confirmed'

when picstatus = '80' then 'Ready for Transfer'

when picstatus = '90' then 'Transfer in progress'

end

) as STATUS 

FROM PROCONSUMER 

WHERE PICSTATUS NOT IN (100,10) 

GROUP BY PICSTATUS "

Open in new window

0
 
LVL 16

Author Comment

by:santoshmotwani
ID: 33582828
server is MSSQL server 2005, This query returns output something like

Transfer Rejected47
 Transfer Cancelled19
 New Sale1632

 Pricing Accepted639
 Sale Confirmed20
 Ready for Transfer1
 Transfer in progress1725


m doing this in perl.

i want to total of all the number that come in output.

mwvisa1 - Can you please help me simply & sum above query plz

Cheers
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33583000
For SQL 2005, try with an analytical function and empty over() clause.  You called it sum so showed with sum(1) which will be same as count(*) of all rows (i.e., you could use count(*) over() - or - count(1) over())
SELECT count(*), sum(1) over(), (

case

when PICSTATUS = '11' then 'Transfer Rejected'

when picstatus = '12' then 'Transfer Cancelled'

when picstatus = '20' then 'New Sale'

when picstatus = '70' then 'Pricing Accepted'

when picstatus = '72' then 'Sale Confirmed'

when picstatus = '80' then 'Ready for Transfer'

when picstatus = '90' then 'Transfer in progress'

end

) as STATUS 

FROM PROCONSUMER 

WHERE PICSTATUS NOT IN (100,10) 

GROUP BY PICSTATUS "

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33583014
Sorry that will give you count of the different groups, so that is not what you want -- try this:
SELECT count(*), (

case

when PICSTATUS is null then 'Totals'

when PICSTATUS = '11' then 'Transfer Rejected'

when picstatus = '12' then 'Transfer Cancelled'

when picstatus = '20' then 'New Sale'

when picstatus = '70' then 'Pricing Accepted'

when picstatus = '72' then 'Sale Confirmed'

when picstatus = '80' then 'Ready for Transfer'

when picstatus = '90' then 'Transfer in progress'

end

) as STATUS 

FROM PROCONSUMER 

WHERE PICSTATUS NOT IN (100,10) 

GROUP BY PICSTATUS WITH ROLLUP"

Open in new window

0
 
LVL 16

Author Closing Comment

by:santoshmotwani
ID: 33583630
Thanks , it solved my issue
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL to update characters in table column 6 97
Very interesting Access query problem. 13 70
Need help subtracting a value within my script 7 42
Help writing a query 6 73
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now