Solved

How to use Group By to Sum a query field

Posted on 2008-10-31
1
210 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now