HAVING/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

General SQL Server Forums

New to SQL Server Programming

HAVING/GROUP BY

Author

Topic

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-17 : 18:04:19

List resellers and total sales for each.
-- Show reseller name, business type, and total sales.
-- List only those resellers having sales exceeding $500,000.
-- 31 Rows

SELECT ResellerName, BusinessType, AnnualSales
FROM dbo.DimReseller AS R
WHERE AnnualSales > 500000

Above sql doesn't work. I am thinking I need HAVING clause or GROUP BY.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-17 : 18:50:45

[EDIT] COUNT to SUM. D'oh!

SELECT t.ResellerName, BusinessType, t.TotalSales
FROM (SELECT ResellerName, SUM(AnnualSales) AS TotalSales
FROM dbo.DimReseller
GROUP BY ResellerName
HAVING SUM(AnnualSales) > 500000) t
JOIN dbo.DimReseller d ON t.ResellerName = d.ResellerName

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-17 : 19:18:50

tkizer, that doesn't return any rows. perhaps sum instead of count?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-17 : 19:20:52

Oh yes, SUM! Lol. Sorry.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-17 : 19:31:54

hmmm...better but doesn't return correct number of rows...

here is the table structure, perhaps other column needs to be used

[dbo].[DimReseller](
[ResellerKey] [int] IDENTITY(1,1) NOT NULL,
[GeographyKey] [int] NULL,
[ResellerAlternateKey] [nvarchar](15) NULL,
[Phone] [nvarchar](25) NULL,
[BusinessType] [varchar](20) NOT NULL,
[ResellerName] [nvarchar](50) NOT NULL,
[NumberEmployees] [int] NULL,
[OrderFrequency] [char](1) NULL,
[OrderMonth] [tinyint] NULL,
[FirstOrderYear] [int] NULL,
[LastOrderYear] [int] NULL,
[ProductLine] [nvarchar](50) NULL,
[AddressLine1] [nvarchar](60) NULL,
[AddressLine2] [nvarchar](60) NULL,
[AnnualSales] [money] NULL,
[BankName] [nvarchar](50) NULL,
[MinPaymentType] [tinyint] NULL,
[MinPaymentAmount] [money] NULL,
[AnnualRevenue] [money] NULL,
[YearOpened] [int] NULL,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-17 : 19:36:35

Please provide sample data and expected output.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-17 : 19:42:11

simple copy and paste won't retain table layout. is there a way to show sample data with format intact. screen capture and pasting it won't work on this forum

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-17 : 22:29:06

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-17 : 22:58:01

tkizer, I went to your link but it still doesn't say how to post sample data, or at least I don't understand what they are talking about. :(

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-03-18 : 00:22:28

Refer this post once...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=193195

Here, OP has posted sample data and expected out put for that sample. In the same way post some sample data and expected data so that any one can understand your requirement in better way.

--
Chandu

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-18 : 09:31:13

I found the solution. I needed another table, not just one. thanks for help tkizer. Thanks bandi.

here it is:

SELECT R.ResellerName, R.BusinessType, SUM(ROUND(S.[OrderQuantity] * S.UnitPrice, 2)) AS TotalSales
FROM dbo.DimReseller AS R
INNER JOIN dbo.FactResellerSales AS S
ON R.ResellerKey = S.ResellerKey
GROUP BY R.ResellerName, R.BusinessType
HAVING SUM(S.OrderQuantity * S.UnitPrice) > 500000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-18 : 12:20:37

Your post didn't have enough information for us to know you needed another table in your query. For future posts, be sure to include all relevant information so that we can help you.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources