Solved

SQL SUM

Posted on 2010-09-01
6
409 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

759 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

23 Experts available now in Live!

Get 1:1 Help Now