Solved

How to use Group By to Sum a query field

Posted on 2008-10-31
1
212 Views
Last Modified: 2010-03-20
I have the following query (in code snippet) which runs perfectly.  However, I need to modify it slightly to sum the column named 'Quantity'.  However, everything I try in creating a GROUP BY line fails.  Can somebody rewrite to group & sum the 'Quantity' column?

Thanks
SELECT     TOP 100 PERCENT item_id AS [Part Number], 'TSI' AS [Distributor Code], CONVERT(decimal(5, 0), qty_on_hand) AS Quantity, '25' AS [% Discount], 
                      CONVERT(decimal(8, 2), price1) AS [Sell Price], CONVERT(varchar, GETDATE(), 101) + '|' AS [Date]
FROM         dbo.p21_view_inv_loc
WHERE     (qty_on_hand > 0) AND (product_group_id LIKE 'mac%')
ORDER BY item_id

Open in new window

0
Comment
Question by:timoteoga
1 Comment
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22852954
Try:


select [part number], [distributor code], [discount], [sell price],[date] , sum(quantity) as Sum_quantity
from  
(SELECT     TOP 100 PERCENT item_id AS [Part Number], 'TSI' AS [Distributor Code], CONVERT(decimal(5, 0), qty_on_hand) AS Quantity, '25' AS [% Discount], 
                      CONVERT(decimal(8, 2), price1) AS [Sell Price], CONVERT(varchar, GETDATE(), 101) + '|' AS [Date]
FROM         dbo.p21_view_inv_loc
WHERE     (qty_on_hand > 0) AND (product_group_id LIKE 'mac%')
) a
group by  [part number], [distributor code], [discount], [sell price],[date] 
order by [part number]

Open in new window

0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

786 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