How to use sum and count functions

Posted by Odette on Stack Overflow See other posts from Stack Overflow or by Odette
Published on 2010-04-23T03:08:32Z Indexed on 2010/04/23 3:13 UTC
Read the original article Hit count: 411

Filed under:

sql

|

debugging

hi guys

I am trying to get the total count and total sum per salesperson but its not working in my WITH query

Currently I have:

WITH CALC1 AS (SELECT B.OTSMAN AS SALESMAN, COUNT(B.ONINUM) AS COUNT, SUM(B.OVSVAL) AS SVAL FROM @[email protected] AS B WHERE B.OCSGRP <> 'INT' AND B.OCSGRP <> 'INX' AND B.OCPSBR IN (@OCPSBR@) AND B.ICMATX <> '705' AND B.OCSGRP NOT LIKE 'S/%' GROUP BY B.OTSMAN, B.ONINUM ORDER BY B.OTSMAN ASC)

SELECT 

SALESMAN,
COUNT, 
SVAL

FROM CALC1

ORDER BY SALESMAN

THE RESULT IS:

AM  1   79.03
AM  40  1337.70
AM  48  4627.15
AM  42  2727.40
AM  1   111.79
AM  1   1110.00
AM  52  3018.77

How do i change my query so I only get one record with the total count and value per salesperson?

AM  1   13,011.84

© Stack Overflow or respective owner

Related posts about sql

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

as seen on SQL Authority - Search for 'SQL Authority'
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL… >>> More
SQL SERVER – Concat Function in SQL Server – SQL Concatenation

as seen on SQL Authority - Search for 'SQL Authority'
Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though… >>> More
Error with SQL Server Setup 2012 on Windows 2012

as seen on Server Fault - Search for 'Server Fault'
I am trying to install SQL Server on Windows 2012. I was able to finally get the wizard up and running after making some changes on the server, but now it fails no matter what I do with the following error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: … >>> More
How can I detect which version of SQL (eg SQL 2008 or SQL Azure)

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to detect which version of SQL I am dealing with to perorm various tasks, I need specifically detect if I am on SQL 2008 or SQL Azure. How can I do this with detection code written in SQL? >>> More
Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder… >>> More

Related posts about debugging

I need help debugging apache with gdb (no debugging symbols found)

as seen on Server Fault - Search for 'Server Fault'
I'm trying to debug a segfault in PHP/Apache running on RHEL4. This is a production server, so I'm trying to install a separate copy of apache and php, and run apache through gdb. When I load httpd through gdb and then do run -X, I get the error (no debugging symbols found)...Error while reading… >>> More
debugging C++ when compared to debugging C

as seen on Stack Overflow - Search for 'Stack Overflow'
HI, I am normally a C programmer. I do regularly debug C programs on unix environment using tools like gdb,dbx. i have never done debugging of big applications of C++. Is that much different from how we debug in C. theoretically i am quite good in C++ but have never got a chance to debug C++ programs… >>> More
How to fix Ogre3d segfault with std::_Rb_tree_insert_and_rebalance ?

as seen on Game Development - Search for 'Game Development'
Hello all. I'm working on a 3d music visualizer using Ogre3d, basically it's a spectrum analizer, a lot like the old xmms plugin: (http)://www.youtube.com/watch?v=_6NKBiwYN24 It works well, the bars are drawn and updated, there are no framerate issues, but it crashes randomly. Sometimes it can run… >>> More
Will logging debugging incur a performance hit if I don't turn debugging on?

as seen on Server Fault - Search for 'Server Fault'
On a Cisco device, I know that enabling debugging can incur a performance hit since debugging has such a high priority on the CPU. I know that to log debugging, you have to set logging up to the debugging level (logging buffered 4096 debugging, for example) and also enable debugging on some feature… >>> More
Debugging unmanaged code while debugging managed code

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, The .NET 3.5 application I am working on consists of bunch of different solutions. Some of these solutions consist of managed code(C#) and others have unmanaged code(C++). Methods written in C# communicate with the ones written in C++. I am trying to trace the dependencies between these various… >>> More