[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL SUM

Posted on 2010-09-01
6
Medium Priority
?
420 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 60

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 60

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 60

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 60

Accepted Solution

by:
Kevin Cross earned 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

656 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