sql - PostgreSQL: Finding the total line total for each product that was sold in the largest order? -

-

i'm college student taking database course, using postgresql. there 1 question seem's give me trouble.

here 2 tables:

table1-orders

fields-id, order_date, customer_id, credit_card_number, cvv, , order_total

table2-order_lines

fields-id, order_id, product_id, quantity, sell_price, , line_total

here question:

what total line_total each product sold in largest order? (largest order_total). enter 0 if product not in largest order. make sure enter both decimal places.

so far, syntax have:

select product_id,  sum (line_total * (1) * quantity) order_lines group product_id;

i able output product_id total sum of line_total wondering how total line_total each product sold in largest order.

should find id of largest order based on order_total? use sub query merge the 2 tables final answer? using sell_price field syntax have above?

first need largest order. done taking first record ordered order_total desc:

select  *    orders order         order_total desc limit   1

then need products in largest order. join order_lines , select distinct product ids:

select  distinct         product_id    (         select  *            orders         order                 order_total desc         limit   1         ) o join    order_lines ol on      ol.order_id = o.id

finally, need join resultset query, substituting sums zeros if product not in largest order:

select  product_id,         case when lo.product_id null sum_line_total else 0 end    (         select  product_id, sum(line_total) sum_line_total            order_lines         group                 product_id         ) ps left join         (         select  distinct                 product_id            (                 select  *                    orders                 order                         order_total desc                 limit   1                 ) o         join    order_lines ol         on      ol.order_id = o.i         ) lo using   (product_id)

we further optimize query not calculating sums products not in largest order, enough going.


Comments

Post a Comment

Popular posts from this blog

ios - Memory not freeing up after popping viewcontroller using ARC -

-
hi project arc based, using uinavigationcontroller make transition between viewcontroller. using profiler analyse happening behind scene memory. noticed when push viewcontroller allocate memory components , when pop not freeing allocated memory. since using arc unable implement dealloc or release component. have analysed in detail , there no memory leak in project. i not using strong property push viewcontroller. here how pushing viewcontroller. viewcontroller *obj = [[viewcontroller alloc] init]; [self.navigationcontroller pushviewcontroller:obj animated:no]; any clue whats going on? should free memory have consumed. please advise your description indicates have retain cycle, leads objects not being deallocated. typical source of retain cycles properties assigned loading nib files (usually declared iboutlet ). two strategies break them , release objects: declare property weak : @property (nonatomic, weak) iboutlet uilabel *statuslabel; set property nil in...
Read more

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more