Solved

SQL SUM

Posted on 2010-09-01
6
415 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 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