is possible thro a single query

Question

Post reply

is possible thro a single query

S-322532

SSCommitted

Points: 1613
More actions
March 20, 2009 at 5:22 am

#95284

Kindly take a look at the table below
Zone Class Item Variety Sales
---------------------------------------------------
YC1 Class-IZIte AVariety1 20000.00
YC1 Class-IZIte BVariety1 15000.00
YC1 Class-IZIte CVariety1 41000.00
YC1 Class-IZIte DVariety2 32000.00
YC1 Class-IZIte EVariety2 28000.00
YC2 Class-IZIte AVariety1 25000.00
YC2 Class-IZIte BVariety1 7500.00
YC2 Class-IZIte EVariety2 43000.00
YC2 Class-IZIte GVariety1 56000.00
YC2 Class-IZIte RVariety2 45000.00
----------------------------------------------------
My query is
how to Count the number of items whose sales sum to 80% of the total Class sales, per CLass , per Zone
Is it possible by a single query ? 🙁
Any other ways of achieving this ?
Help me plz.
Thanks
Sree

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

ramu.valleti SSCrazy Points: 2229 More actions · Answer 1

what do you mean by "single query".?

You can use sub queries to achieve this.

Regards,

Ramu

Ramu
No Dream Is Too Big....!

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 2

ramu.valleti (3/20/2009)
what do you mean by "single query".?
You can use sub queries to achieve this.
Regards,
Ramu

Cool... let's see it, Ramu.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

S-322532 SSCommitted Points: 1613 More actions · Answer 3

Hi,

Thanks for the reply.

I should have expressed it clearly or more correctly.

Is it possible to achieve it with out using multiple statements (exclude subqueries as they are part of the main query) say for example without using cursors, stored procedures.

Sorry for the inconvenience 🙂

Thanks,

Sree

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 4

S (3/20/2009)
Hi,
Thanks for the reply.
I should have expressed it clearly or more correctly.
Is it possible to achieve it with out using multiple statements (exclude subqueries as they are part of the main query) say for example without using cursors, stored procedures.
Sorry for the inconvenience 🙂
Thanks,
Sree

Absolutely... and if you'll post the data in a readily consumable format and the table creation script to hold the data, I'm pretty sure that you'll get an excellent fully tested bit of code to satisfy your requirement. Please see the link in my signature below for what I mean by a "readily consumable format". Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 5

Jeff Moden (3/20/2009)
ramu.valleti (3/20/2009)
what do you mean by "single query".?
You can use sub queries to achieve this.
Regards,
Ramu
Cool... let's see it, Ramu.

Ramu... you there? I'd still like to see the solution you suggested in the form of code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

S-322532 SSCommitted Points: 1613 More actions · Answer 6

Hi,

Thanks for the reply and interest. 🙂

Kindly find attached files containing data in .xls file,.txt file(with data in pipe delimited format) and table structure in .txt file.

Thanks,

Sree

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 7

S (3/22/2009)
Hi,
Thanks for the reply and interest. 🙂
Kindly find attached files containing data in .xls file,.txt file(with data in pipe delimited format) and table structure in .txt file.
Thanks,
Sree

Heh... you either didn't read the article, didn't understand the article, or chose to ignore the article. An Excel spreadsheet is not the "readily consumable format" that the article speaks of. Multiple unioned Select statements following a single insert statement are. Ironically, it probably took you longer to make the spreadsheet than it would to do it as requested.

Lemme know when you're ready with readily consumable data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

S-322532 SSCommitted Points: 1613 More actions · Answer 8

Hi,

Kindly find attached the file for table structure and insert statement in txt files.

Thanks,

Sree 🙂

LutzM SSC Guru Points: 107049 More actions · Answer 9

Would you be a little more specific on how do determine, which Items should be used to sum for the 80%?

Example: Your requirement of "making 80% of the total Class sales" can't be achieved in the first group, since none of them add up to exactly 80%.

1,2,3,4 come closest (79.4%) but still no 80%...

2,3,4,5 exceed 80%, but are not as close as the 4 above.

The count for both would be 4, which will give you the same result but using different ways.

Below please find an example, where those two methods will give different counts.

Example:

ItemSales

120000

214000

330000

426000

528000

63000

75000

88000

9800

10200

111000

How many items would you expect to show up as a result?

1,3,4,5,6,9 exact match 80% (118800)

1,3,4,5,7 first group exceeding 80% (109000)

So, what defines "80% of class sales?"

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 10

S (3/22/2009)
Hi,
Kindly find attached the file for table structure and insert statement in txt files.
Thanks,
Sree 🙂

Heh... see? Someone already started working on your stuff since you made it easy for them. Me too! Here's a tested solution...

--===== Identify a database to "play" in

USE TempDB

--DROP TABLE dbo.Sales

GO

--===== Create the test table using the code provided by the OP

CREATE TABLE [dbo].[sales](

[sno] [int] IDENTITY(1,1) NOT NULL,

[zone] [varchar](20) NULL,

[Class] [varchar](10) NULL,

[Item] [varchar](10) NULL,

[Variety] [varchar](50) NULL,

[sales] [decimal](12, 2) NULL

) ON [PRIMARY]

--===== Populate the test table using the code provided by the OP

insert into sales

select 'YC1','Class-IZ','Ite A','Variety1',20000.00 union

select 'YC1','Class-IZ','Ite B','Variety1',15000.00 union

select 'YC1','Class-IZ','Ite C','Variety1',41000.00 union

select 'YC1','Class-IZ','Ite D','Variety2',32000.00 union

select 'YC1','Class-IZ','Ite E','Variety2',28000.00 union

select 'YC2','Class-IZ','Ite A','Variety1',25000.00 union

select 'YC2','Class-IZ','Ite B','Variety1',7500.00 union

select 'YC2','Class-IZ','Ite E','Variety2',43000.00 union

select 'YC2','Class-IZ','Ite G','Variety1',56000.00 union

select 'YC2','Class-IZ','Ite R','Variety2',45000.00

select * from sales

--how to Count the number of items whose sales sum to 80% of the total Class sales, per CLass , per Zone

--===== Demo the solution to the problem... none of the data met the 80% requirement so I used 25%

-- just to prove that the code works as advertised. Change it if you need to. See comment in

-- the HAVING clause below.

;WITH

cteTotalClassZone AS

(

SELECT Class, Zone, SUM(Sales) AS TotalSales

FROM dbo.Sales

GROUP BY Class, Zone

)

SELECT s.Class, s.Zone, s.Item,

COUNT(*) AS ItemCount,

SUM(s.Sales) AS ItemSales,

t.TotalSales AS ClassZoneSales,

SUM(s.Sales)/t.TotalSales*100 AS PercentOfClassZoneSales

FROM dbo.Sales s

INNER JOIN

cteTotalClassZone t

ON s.Class = t.Class

AND s.Zone = t.Zone

GROUP BY s.Class, s.Zone, s.Item, t.TotalSales

HAVING SUM(s.Sales)/t.TotalSales*100 >= 25 --<<LOOK!!! Change this to the % you need!!!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 11

Agh... the code above needed a tweek... sorry about the error... it failed one scenario so I fixed it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramu.valleti SSCrazy Points: 2229 More actions · Answer 12

Yes Jeff Moden,

I'm back..!

The query i was thinking is similar to your query, and here is mine..

SELECT t1.zone,t1.Class,COUNT(Item) Cnt

FROM

(

SELECT zone,Class,Item,SUM(sales) sales

FROM sales

GROUP BY zone,Class,Item

)t1 INNER JOIN

(

SELECT zone,Class,SUM([sales]) sales

FROM [sales]

GROUP BY zone,Class

)t2 ON t1.zone=t2.zone AND t1.Class=t2.Class

WHERE t1.sales>=(t2.sales*0.10)

--ONE CAN CHANGE THE PERCENTAGE OR

--EVEN EQUALITY SYMBOL(>= OR =)

GROUP BY t1.zone,t1.Class

I've never used WITH in my queries..probably this is the time to start off, if the performance is better than INNER JOINS...

Jeff Moden: t1 and t2 are the subqueries which i was talking about to achieve this.

Regards,

Ramu

Ramu
No Dream Is Too Big....!

S-322532 SSCommitted Points: 1613 More actions · Answer 13

Wow :Wow: really amazed by the overwhelming response from the Gurus. 🙂

Lutz, 80 % is mentioned juz for a limit to check against. :-). Thanks for the care u shown.

Jeff, thanks for ur effort and repeated queries for helping me and to answer my problem.

Ram, thanks for correcting me to coin the requirement exactly to the point and passing me another solution.

Jeff, may i know what is the tweak and the failed scenario plz.

Once again Thanks to all the Gurus. 🙂

Thanks,

Sree

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 14

ramu.valleti (3/22/2009)
Yes Jeff Moden,
I'm back..!
The query i was thinking is similar to your query, and here is mine..
SELECT t1.zone,t1.Class,COUNT(Item) Cnt
FROM
(
SELECT zone,Class,Item,SUM(sales) sales
FROM sales
GROUP BY zone,Class,Item
)t1 INNER JOIN
(
SELECT zone,Class,SUM([sales]) sales
FROM [sales]
GROUP BY zone,Class
)t2 ON t1.zone=t2.zone AND t1.Class=t2.Class
WHERE t1.sales>=(t2.sales*0.10)
--ONE CAN CHANGE THE PERCENTAGE OR
--EVEN EQUALITY SYMBOL(>= OR =)
GROUP BY t1.zone,t1.Class
I've never used WITH in my queries..probably this is the time to start off, if the performance is better than INNER JOINS...
Jeff Moden: t1 and t2 are the subqueries which i was talking about to achieve this.
Regards,
Ramu

Cool... yeah, the WITH thingy is what they call a Common Table Expression or just "CTE" for short. The good part about using just derived tables like you did is that it also works in SQL Server 2000.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)