Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to use Group By to Sum a query field

Posted on 2008-10-31
1
Medium Priority
?
220 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 2000 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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…
Suggested Courses

571 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