query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

Leonel
Starting Member

10 Posts

Posted - 2011-06-30 : 20:52:26

i need some help with this querry.
I want to report the item_numer, description, and the total of quantity picked for the whole year.
so i have this view, PickDetails(Item_number, Description, Pick_quantity, creation_date)
when i run the following query i get reapeted rows
--------------------------------------------
select distinct item_number,description, sum(picked_quantity)as QTY

from dbo.dbo_vPickDetails
WHERE creation_date BETWEEN '2010-06-01' AND '2011-06-01'
gROUP BY item_number,description,picked_quantity

Order by QTY DESC
this is the result

20713 216 FIBER SINGLE-MODE AERIAL w/ LOOSE TUBE BUFFER 30503.0000
20713 216 FIBER SINGLE-MODE AERIAL w/ LOOSE TUBE BUFFER 25164.0000
20713 216 FIBER SINGLE-MODE AERIAL w/ LOOSE TUBE BUFFER 25114.0000
20713 216 FIBER SINGLE-MODE AERIAL w/ LOOSE TUBE BUFFER 25085.0000
20713 216 FIBER SINGLE-MODE AERIAL w/ LOOSE TUBE BUFFER 24081.0000
20713 216 FIBER SINGLE-MODE AERIAL w/ LOOSE TUBE BUFFER 13750.000020712 12 Strand Single Mode Aerial non amored fiber optic cable flexlink. 12524.0000
20297 48 FIBER SINGLE-MODE AERIAL W/LOOSE TUBE BUFFER NON - ARMOR PART# 0048HBS1AFESJA 7292.0000
20297 48 FIBER SINGLE-MODE AERIAL W/LOOSE TUBE BUFFER NON - ARMOR PART# 0048HBS1AFESJA 6180.0000
20712 12 Strand Single Mode Aerial non amored fiber optic cable flexlink. 6056.0000
20712 12 Strand Single Mode Aerial non amored fiber optic cable flexlink. 6014.0000
20713 216 FIBER SINGLE-MODE AERIAL w/ LOOSE TUBE BUFFER 5500.0000i get 7 records for item_numer 20713
i want to get 1 record and the sum of quantity

20713 216 FIBER SINGLE-MODE AERIAL w/ LOOSE TUBE BUFFER 149197if you can help me would be nice

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-30 : 21:03:04

Remove picked_quantity from the GROUP BY clause:

select distinct item_number,description, sum(picked_quantity)as QTY

from dbo.dbo_vPickDetails 
WHERE creation_date BETWEEN '2010-06-01' AND '2011-06-01'
gROUP BY item_number,description   ,picked_quantity

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-04 : 10:49:11

Also always use unambigious date format
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources