sum and group by - 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

SQL Server 2008 Forums

Transact-SQL (2008)

sum and group by

Author

Topic

john.lam
Starting Member

12 Posts

Posted - 2010-01-29 : 08:00:46

Hi all,

I have a table with the following structure:


CREATE TABLE [dbo].[udt_HireItems](
	[RecordId] [int] IDENTITY(1,1) NOT NULL,
	[Barcode] [varchar](50) NOT NULL,
	[Department] [nvarchar](256) NOT NULL,
	[ProductDescription] [nvarchar](256) NOT NULL,
	[Qty] [int] NOT NULL,
 CONSTRAINT [PK_udt_HireItems] PRIMARY KEY CLUSTERED 
(
	[RecordId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I need to generate a view for the report, which will return a report with the following fields: Rinking, Barcode, Department, ProductDescription and Number of Times Being Hired Out (i.e. GROUP BY Barcode and SUM(Qty)).

My problems:
Is there a way to return all the related data in one view? (Since if I use "GROUP BY Barcode", I can only place Barcode in the SELECT statement).
The other problem is if I put "ORDER BY SUM(Qty) DESC", it returns the correct order in the design mode in SQL Server 2008 (I'm using Express version) but in the incorrect order when I save and execute the view. In this case, I found that I need to execute the view and put the bit "ORDER BY SUM(Qty) DESC" out of the view (i.e. in the query panel).

Anyone can give me some hints on these?

Thanks

John Lam

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-01-29 : 19:13:28

I'm not sure what you mean by "Rinking" so we'll just ignore that for now.

If the Barcode defines the product and the Department and ProductDescription are "constant" with a single barcode grouping, you could simply:
[CODE]
select
Barcode,
Department,
ProductDescription,

count(*) NumberOfSales,
sum(qty) TotalSales
from
udt_HireItems
group by
Barcode,
Department,
ProductDescription
[/CODE]
However, since things are never as simple as we might hope, you can combine row data with aggregated data by using the OVER clause:
[CODE]
select
RecordID, -- Rinking ???
Barcode,
Department,
ProductDescription,

count(*) over(partition by Barcode) NumberOfSales,
sum(qty) over(partition by Barcode) TotalSales
from
udt_HireItems
[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-01-29 : 19:22:31

As to your ordering issue...

1) SQL tables (virtual or otherwise) are a collection of unordered data.
2) The SELECT statement that defines the view is not the same as the SELECT statement that uses the view. One defines which records are included in the view, the other defines which subset of those records are returned from the view. In your case, the first one is ordered but the second is not.
3) If you want ordered data when you return records, use the ORDER BY clause.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

john.lam
Starting Member

12 Posts

Posted - 2010-01-29 : 20:27:09

Hi Bustaz Kool,

Thanks your for response. Sorry for my typo, "Rinking" should be "Ranking" as the report returns the top 20/50 items.

You are right, there is a chance that multiple barcodes will have the same ProductDescription and/or Department, but If I use OVER PARTITION BY, it will return more rows than I suppose to get for the report. Is there any way to solve this problem? (Actually I can add a DepartmentId together with the Department in the table?)

quote:
Originally posted by Bustaz Kool

I'm not sure what you mean by "Rinking" so we'll just ignore that for now.

If the Barcode defines the product and the Department and ProductDescription are "constant" with a single barcode grouping, you could simply:
[CODE]
select
Barcode,
Department,
ProductDescription,

count(*) NumberOfSales,
sum(qty) TotalSales
from
udt_HireItems
group by
Barcode,
Department,
ProductDescription
[/CODE]
However, since things are never as simple as we might hope, you can combine row data with aggregated data by using the OVER clause:
[CODE]
select
RecordID, -- Rinking ???
Barcode,
Department,
ProductDescription,

count(*) over(partition by Barcode) NumberOfSales,
sum(qty) over(partition by Barcode) TotalSales
from
udt_HireItems
[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

John Lam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-30 : 01:05:51

quote:
Originally posted by john.lam

Hi Bustaz Kool,

Thanks your for response. Sorry for my typo, "Rinking" should be "Ranking" as the report returns the top 20/50 items.

You are right, there is a chance that multiple barcodes will have the same ProductDescription and/or Department, but If I use OVER PARTITION BY, it will return more rows than I suppose to get for the report. Is there any way to solve this problem? (Actually I can add a DepartmentId together with the Department in the table?)

do you mean to say that you need ignore some of them even if they happen to be of same rank as included ones?

john.lam
Starting Member

12 Posts

Posted - 2010-01-30 : 01:37:56

quote:
Originally posted by visakh16

quote:
Originally posted by john.lam

Hi Bustaz Kool,

Thanks your for response. Sorry for my typo, "Rinking" should be "Ranking" as the report returns the top 20/50 items.

You are right, there is a chance that multiple barcodes will have the same ProductDescription and/or Department, but If I use OVER PARTITION BY, it will return more rows than I suppose to get for the report. Is there any way to solve this problem? (Actually I can add a DepartmentId together with the Department in the table?)

do you mean to say that you need ignore some of them even if they happen to be of same rank as included ones?

Yep.

John Lam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-30 : 02:00:38

so you want how many from top?

john.lam
Starting Member

12 Posts

Posted - 2010-01-30 : 02:12:43

quote:
Originally posted by visakh16

so you want how many from top?

Top 20

John Lam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources