proper query syntax 1

[DotNetGnat]

Guys,

does this kind of query work in sybase database?

select         T.location, 
        sum(T.Price) 'TotPrice', 
        Count(distinct T.ItemNum) 'TotItems' 
from         (select     t1.location 'location', 
                t5.price 'price', 
                t2.itemnum 'ItemNum'
         from         table t1, 
                table t2, 
                table t3, 
                table t4, 
                table t5
         where         t1.id = t2.id so on...) T
Group by     T.Location

i tried but i am getting error near ')'. What is the proper syntax for query like this.

Thanks

-DNG

 

[micha123]

As much as I know, this syntax works in other databases (such as MS-SQL), but was an undocummented (!!) command in prior SYBASE releases.
I couldn't find information on this syntax in newer releases.

You can try this syntax instead:

select t1.location,
sum(t5.Price) 'TotPrice',
Count(distinct t2.ItemNum) 'TotItems'
from table t1,
table t2,
table t3,
table t4,
table t5
where t1.id = t2.id so on...
Group by t1.Location

 

[DotNetGnat]

i know that works...but i had some duplicates and was trying to eliminate using the query i posted...

thanks

-DNG

 

[micha123]

Hi, DNG,

I think that the results would be the same, but your original query would build a temporary table to hold the results of the subquery first (if it would work...).
Knowing SYBASE optimizer - I suggest you to write it as I offered.

M.

 

[DotNetGnat]

Thanks for your help again...here is the complete situation

here is my data again...

I have the following table

Location| Price    | ItemNum | EndDate
_______________________________________
name1   | 10       |   111   | 06/01/2005
name1   | 10       |   111   | 06/20/2005
name1   | 20       |   112   | 06/03/2005
name2   | 20       |   113   | 06/04/2005
name3   | 15       |   114   | 06/06/2005
name3   | 15       |   115   | 06/07/2005

I have the following query...

SELECT Location, SUM(Price) as totprice, Count(DISTINCT ItemNum) as totitems FROM mytable GROUP By Location

and i get the output as

Location| totprice | totitems
______________________________
name1   | [red]40[/red]       |   2
name2   | 20       |   1
name3   | 30       |   2

BUT I WANT TO ONLY CONSIDER THE SECOND ONE IN BELOW OF THESE:

name1 | 10 | 111 | 06/01/2005
name1 | 10 | 111 | 06/20/2005

i mean MAX(ENDDATE)..so that i can get a totprice of 30 instead of 40

Thanks

-DNG

 

[micha123]

mmmmm....

Now I understand... (-:

So, you can use a subquery:

SELECT t1.Location, t1.SUM(Price) as totprice,
Count(DISTINCT t1.ItemNum) as totitems
FROM mytable t1
WHERE EndDate = (select max(EndDate) from mytable t2
where t2.Location = t1.Location
and t2.ItemNum = t1.ItemNum)
GROUP By Location

If this causes performance problems - you can use a temporary table (#tmp) to hold the subquery results and then joining it with the table.

The only problem here is if you have 2 rows with the same location, ItemNum and EndDate. Do you?

Please let me know if this reaches your needs.

M.

 

[DotNetGnat]

what if my the data comes from two tables...

Table 1
EquipNumber
111
112
113
114
115

there is also lot of other information in this table that i use in my query...i made it simple here

Table 2
EquipNumber   |Price  |   EndDate | Location
111           |  10   | 06/01/2005|  name1
111           |  10   | 06/20/2005|  name1
112           |  20   | 06/03/2005|  name1
113           |  20   | 06/04/2005|  name2
114           |  15   | 06/05/2005|  name3
115           |  15   | 06/06/2005|  name3

I want the output as

Location | TotalNumbers | TotalPrice
Name1    |     2        |      30
Name2    |     1        |      20
Name3    |     2        |      30

the thing here is - if same location as duplicate EquipNumbers then i want to consider only the one with the max(EndDate). Please use both the tables in the query...

t1.EquipNumber=t2.EquipNumber


Thanks

-DNG

 

[micha123]

You can add all the joins and join conditions you need,
and always keep this condition in the where clause:

(WHERE...) t1.EndDate = (select max(EndDate) from mytable t2
where t2.Location = t1.Location
and t2.ItemNum = t1.ItemNum)

This will bring you the max(EndDate) row if Location+Item have duplicates, and if not - it will bring the one row that exists.

M.

 

[DotNetGnat]

thanks so much for your help muicha123..i will try that and get back to you...

-DNG

 

[DotNetGnat]

micha123,

just wanted to let you know that worked like a charm..thanks...

sorry for typo in your name in my previous post..

-DNG

 

[micha123]

I'm glad I could help you,
and don't worry about the name (it's not my real one anyway, so... )