Problem with query

Question

Problem with query

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
August 23, 2012 at 5:39 am

#280143

Hi,
I have the following scenario as
------- Astore--------------------------Bstore
--------Parker Reynolds Uniball-------Parker Reynolds Uniball
Pen1--10 20 30-----------------------20 30 40
i want the query which would give me the max for Astore i.e 30 and max for Bstore i.e. 40 i.e 30 + 40 =70. There are many pens like pen1, pen2 etc.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ant-Green SSC Guru Points: 113499 More actions · Answer 1

Can you post the DDL and some sample data with expected outcomes, its unclear from your initial post what the table structure is.

Lowell SSC Guru Points: 323518 More actions · Answer 2

the table layout is critical here. you've got to provide the DDL to get a good answer;

are Astore and bstore columns in the same table, or two differnet tables? or two different rows in the same table?

are the values you mentioned in Pen1 seperate rows, or columns in the same row? all munged together as a single string?

is Pen1 a seperate table?

my wild guess is it looks like this?

Create Table AllStores(Astore varchar(30),Bstore varchar(30) )

INSERT INTO AllStores SELECT 'Parker Reynolds Uniball','Parker Reynolds Uniball'

once you provide the DDL, we can show you how to use the GROUP BY and MAX to get your data

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sandeep rawat Ten Centuries Points: 1317 More actions · Answer 3

table def

table

(

price int,

typeofpen varchar (20)

)

query

-----

,WITH Temp AS

(MAX(price ) OVER(PARTITION BY typeofpen) AS price

FROM

table

)

SELECT SUM(mx) FROM temp

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

sandeep rawat (8/23/2012)
table def
table
(
price int,
typeofpen varchar (20)
)
query
-----
,WITH Temp AS
(MAX(price ) OVER(PARTITION BY typeofpen) AS price
FROM
table
)
SELECT SUM(mx) FROM temp

Curious, this doesn't even look like it comes close to the vaguely worded request from the OP.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

Lynn Pettis (8/23/2012)
sandeep rawat (8/23/2012)
table def
table
(
price int,
typeofpen varchar (20)
)
query
-----
,WITH Temp AS
(MAX(price ) OVER(PARTITION BY typeofpen) AS price
FROM
table
)
SELECT SUM(mx) FROM temp
Curious, this doesn't even look like it comes close to the vaguely worded request from the OP.

Not to mention that I've never run across a business case that requires adding two prices together. Extended price (qty * price) yes.

It is sort of like adding 2012-01-01 to 2012-12-31 - sure you get a number but what is the meaning in the physical universe? Perhaps this calls for DBCC_MULTI_DIMENSIONAL_SPACE_WARP to resolve.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

sandeep rawat Ten Centuries Points: 1317 More actions · Answer 6

Lynn Pettis (8/23/2012)
sandeep rawat (8/23/2012)
table def
table
(
price int,
typeofpen varchar (20)
)
query
-----
,WITH Temp AS
(MAX(price ) OVER(PARTITION BY typeofpen) AS price
FROM
table
)
SELECT SUM(mx) FROM temp
Curious, this doesn't even look like it comes close to the vaguely worded request from the OP.

Hi as i understood there is table contain price and type of pen

requirement - as i understood - sum of max of price of each type of pen.

In my solution - I used partition over close to find max of each group (stored in temp result set CTE)

in next part I SUM all those ( got in temp result) .

what was the requirement

🙂

sandeep

rhythmk SSCertifiable Points: 7271 More actions · Answer 7

sandeep rawat (8/23/2012)
Lynn Pettis (8/23/2012)
sandeep rawat (8/23/2012)
table def
table
(
price int,
typeofpen varchar (20)
)
query
-----
,WITH Temp AS
(MAX(price ) OVER(PARTITION BY typeofpen) AS price
FROM
table
)
SELECT SUM(mx) FROM temp
Curious, this doesn't even look like it comes close to the vaguely worded request from the OP.
Hi as i understood there is table contain price and type of pen
requirement - as i understood - sum of max of price of each type of pen.
In my solution - I used partition over close to find max of each group (stored in temp result set CTE)
in next part I SUM all those ( got in temp result) .
what was the requirement
🙂
sandeep

But was it the actual requirement ?

I guess only OP can explain it 😉

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂