Solved

SQL SUM

Posted on 2010-09-01
6
413 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Please help with the below query - SQL Server 11 17
Dcount using a date in a table compared to today's date 3 28
Help Required 3 90
SQL Error - Query 6 24
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

808 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