Link to home
Start Free TrialLog in
Avatar of Sara bhai
Sara bhaiFlag for India

asked on

How to tune the database in postgresql for largest size

How to tune the database in postgresql for largest size, database contains near about 4700 tables and some of the table size like 15gb, 10gb, 8905MB and database size 6580GB
when I am query for fifty/sixty table union all operation with where search criteria with using group by avg,sum function it will take much much time some times it will take 10-12 hrs or more than that and i need query like this with different criteria are more than 100s. so to take result it time taken.
Now  what i need to get better performance for this ?
Avatar of BigSchmuh
BigSchmuh
Flag of France image
If your data model is "optimized" (some de-normalization exists to query on child tables criteria), your applications are well designed and SQL queries are tuned, there is only one way to enhance the performance:
-move to a faster storage subsystem : more spindles, SSD instead of HDD
-enlarge the cache : io subsystem write back cache (backed), Postgre cache, move to raw drives access
Avatar of zappafan2k2
zappafan2k2
BigSchmuh has some good ideas, if your data model is optimized.

If not, however, having worked with Postgres for some years now, albeit on a much smaller dataset than yours, I would have a few suggestions.
First, adding memory, and then allowing PG to use it will help some.  In the postgresql.conf file, specifically look at
    shared_buffers
    work_mem
    maintenance_work_mem
Here's a link for more information

Second, use the EXPLAIN command.  Don't use EXPLAIN ANALYZE, though.
Read up on indexes:
http://www.postgresql.org/docs/9.1/static/indexes.html

It's a bit of an art.  You have to figure out what parts of the queries are killing you, then address those parts. Maybe you could create a temporary table with the aggregated columns, so that you don't have to do those averages and sums on the fly.
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
4700 tables ???!!!
Shouldn't you be using table partitions ?

Wait for PostgreSQL 9.2 ?
http://www.postgresql.org/about/featurematrix/detail/218/
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
Are you using pg-poolII ?
Avatar of Sara bhai
Sara bhai
Flag of India image

ASKER

I have need any best practices related to tune the performance for the larger database size, or any postgresql setting the enhance the performance or any tablespace concept related, temp_tablespace related uses, where postgres handles the grouping operation?
Standardized documentation for postgresql
Avatar of BigSchmuh
BigSchmuh
Flag of France image
Can you look at the query plan and estimate how many records are involved for each step ?
Then, you will better see your performance problem.
Avatar of zappafan2k2
zappafan2k2
With all due respect, I feel like you're wasting both your and our time.  You haven't given any idea of your level of expertise of being a DBA.  I don't know if this is a pet project, or if this is a commercial endeavor.  If it's the latter, I would recommend hiring a consultant.  If it's the former, then the PostgreSQL main documentation page is a good place to start.

Your questions are vague, and you haven't given any specific information other than the fact that you have a monstrosity of a database that you're trying to manage, with little or no experience with PostgreSQL.

I don't claim to speak for anyone else here, but I honestly think that this forum is the wrong place for your situation.
Avatar of Sara bhai
Sara bhai
Flag of India image

ASKER

Database tables are creating at end of the month for each day records their is separate table for transaction and lookup value if changes it added in it with date field.
Suppose table_tran_2010_01_01 this is for 1 Jan 2010 and its some lookup values in lookup_2010 table holds.three to five different lookup are their.
So using lookup I need to find particular type records form  table_tran_2010_01_01,  table_tran_2010_01_02 ,  table_tran_2010_01_03 and so on upto suppose  table_tran_2010_01_31 with Union All and then apply group for SUM or AVG values.
Avatar of Sara bhai
Sara bhai
Flag of India image

ASKER

In each transaction table their are six fields of type character varying having length 1073741824 , is it because of this table size is heavy or not ?
if yes then how to reduce this unused spaced for the character varying columns.
Avatar of BigSchmuh
BigSchmuh
Flag of France image
Having a column definition allowing for a Varchar of 1GB long does not mean that all cells are using a 1GB space...but it disables all indexing capability in those columns.

As pointed out by zappafan2k2, I suggest to hire a PostgreSQL expert to better help you or to, at least, to post us some SQL and their query plan to better understand where the performance problem is
Avatar of Sara bhai
Sara bhai
Flag of India image

ASKER

ok query Plan is here

 Aggregate  (cost=24283541.36..24283541.37 rows=1 width=72)
   ->  Append  (cost=1027955.04..24283541.13 rows=29 width=72)
         ->  Aggregate  (cost=1027955.04..1027955.06 rows=1 width=27)
               InitPlan 1 (returns $0)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_01  (cost=43774.60..1027952.71 rows=20 width=27)
                     Recheck Cond: (site_id = $0)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 2) OR (hashed SubPlan 3)))
                     ->  Bitmap Index Scan on unified_search_2012_07_01_site_id  (cost=0.00..43751.14 rows=1163699 width=0)
                           Index Cond: (site_id = $0)
                     SubPlan 2
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 3
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=612423.59..612423.61 rows=1 width=28)
               InitPlan 4 (returns $3)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_02  (cost=131481.45..612421.37 rows=6 width=28)
                     Recheck Cond: ((site_id = $3) AND (condition_rollup = 1))
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND ((hashed SubPlan 5) OR (hashed SubPlan 6)))
                     ->  BitmapAnd  (cost=131458.00..131458.00 rows=212795 width=0)
                           ->  Bitmap Index Scan on unified_search_2012_07_02_site_id  (cost=0.00..36747.02 rows=742395 width=0)
                                 Index Cond: (site_id = $3)
                           ->  Bitmap Index Scan on idx_unified_search_2012_07_02_condition_rollup  (cost=0.00..94710.73 rows=1915157 width=0)
                                 Index Cond: (condition_rollup = 1)
                     SubPlan 5
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 6
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=848692.05..848692.07 rows=1 width=27)
               InitPlan 7 (returns $6)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_03  (cost=35592.12..848689.76 rows=14 width=27)
                     Recheck Cond: (site_id = $6)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 8) OR (hashed SubPlan 9)))
                     ->  Bitmap Index Scan on unified_search_2012_07_03_site_id  (cost=0.00..35568.66 rows=736228 width=0)
                           Index Cond: (site_id = $6)
                     SubPlan 8
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 9
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=817437.21..817437.23 rows=1 width=27)
               InitPlan 10 (returns $9)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_04  (cost=21839.51..817434.87 rows=21 width=27)
                     Recheck Cond: (site_id = $9)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 11) OR (hashed SubPlan 12)))
                     ->  Bitmap Index Scan on unified_search_2012_07_04_site_id  (cost=0.00..21816.05 rows=1171551 width=0)
                           Index Cond: (site_id = $9)
                     SubPlan 11
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 12
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=749203.07..749203.09 rows=1 width=28)
               InitPlan 13 (returns $12)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_05  (cost=19599.72..749200.73 rows=21 width=28)
                     Recheck Cond: (site_id = $12)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 14) OR (hashed SubPlan 15)))
                     ->  Bitmap Index Scan on unified_search_2012_07_05_site_id  (cost=0.00..19576.26 rows=1054795 width=0)
                           Index Cond: (site_id = $12)
                     SubPlan 14
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 15
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=750439.02..750439.04 rows=1 width=28)
               InitPlan 16 (returns $15)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_06  (cost=19798.64..750436.68 rows=20 width=28)
                     Recheck Cond: (site_id = $15)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 17) OR (hashed SubPlan 18)))
                     ->  Bitmap Index Scan on unified_search_2012_07_06_site_id  (cost=0.00..19775.19 rows=1065317 width=0)
                           Index Cond: (site_id = $15)
                     SubPlan 17
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 18
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=749303.32..749303.34 rows=1 width=27)
               InitPlan 19 (returns $18)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_07  (cost=19798.67..749301.00 rows=19 width=27)
                     Recheck Cond: (site_id = $18)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 20) OR (hashed SubPlan 21)))
                     ->  Bitmap Index Scan on unified_search_2012_07_07_site_id  (cost=0.00..19775.21 rows=1065320 width=0)
                           Index Cond: (site_id = $18)
                     SubPlan 20
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 21
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=840775.26..840775.28 rows=1 width=27)
               InitPlan 22 (returns $21)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Index Scan using unified_search_2012_07_08_site_id on unified_search_2012_07_08  (cost=23.45..840772.90 rows=24 width=27)
                     Index Cond: (site_id = $21)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 23) OR (hashed SubPlan 24)))
                     SubPlan 23
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 24
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=801806.63..801806.65 rows=1 width=27)
               InitPlan 25 (returns $24)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_09  (cost=20986.12..801804.28 rows=23 width=27)
                     Recheck Cond: (site_id = $24)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 26) OR (hashed SubPlan 27)))
                     ->  Bitmap Index Scan on unified_search_2012_07_09_site_id  (cost=0.00..20962.66 rows=1124971 width=0)
                           Index Cond: (site_id = $24)
                     SubPlan 26
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 27
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=770877.32..770877.34 rows=1 width=27)
               InitPlan 28 (returns $27)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_10  (cost=20178.32..770874.97 rows=22 width=27)
                     Recheck Cond: (site_id = $27)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 29) OR (hashed SubPlan 30)))
                     ->  Bitmap Index Scan on unified_search_2012_07_10_site_id  (cost=0.00..20154.86 rows=1081804 width=0)
                           Index Cond: (site_id = $27)
                     SubPlan 29
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 30
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=800191.33..800191.35 rows=1 width=27)
               InitPlan 31 (returns $30)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_11  (cost=21059.50..800188.98 rows=22 width=27)
                     Recheck Cond: (site_id = $30)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 32) OR (hashed SubPlan 33)))
                     ->  Bitmap Index Scan on unified_search_2012_07_11_site_id  (cost=0.00..21036.04 rows=1128888 width=0)
                           Index Cond: (site_id = $30)
                     SubPlan 32
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 33
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=810688.20..810688.22 rows=1 width=27)
               InitPlan 34 (returns $33)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_12  (cost=21455.22..810685.86 rows=21 width=27)
                     Recheck Cond: (site_id = $33)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 35) OR (hashed SubPlan 36)))
                     ->  Bitmap Index Scan on unified_search_2012_07_12_site_id  (cost=0.00..21431.76 rows=1150715 width=0)
                           Index Cond: (site_id = $33)
                     SubPlan 35
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 36
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=790567.20..790567.22 rows=1 width=27)
               InitPlan 37 (returns $36)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_13  (cost=20911.84..790564.88 rows=18 width=27)
                     Recheck Cond: (site_id = $36)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 38) OR (hashed SubPlan 39)))
                     ->  Bitmap Index Scan on unified_search_2012_07_13_site_id  (cost=0.00..20888.38 rows=1125735 width=0)
                           Index Cond: (site_id = $36)
                     SubPlan 38
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 39
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=811068.30..811068.32 rows=1 width=27)
               InitPlan 40 (returns $39)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_14  (cost=21836.38..811065.98 rows=18 width=27)
                     Recheck Cond: (site_id = $39)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 41) OR (hashed SubPlan 42)))
                     ->  Bitmap Index Scan on unified_search_2012_07_14_site_id  (cost=0.00..21812.92 rows=1170600 width=0)
                           Index Cond: (site_id = $39)
                     SubPlan 41
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 42
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=1037340.09..1037340.11 rows=1 width=27)
               InitPlan 43 (returns $42)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_15  (cost=27565.33..1037337.74 rows=23 width=27)
                     Recheck Cond: (site_id = $42)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 44) OR (hashed SubPlan 45)))
                     ->  Bitmap Index Scan on unified_search_2012_07_15_site_id  (cost=0.00..27541.88 rows=1477886 width=0)
                           Index Cond: (site_id = $42)
                     SubPlan 44
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 45
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=843415.79..843415.81 rows=1 width=27)
               InitPlan 46 (returns $45)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Index Scan using unified_search_2012_07_16_site_id on unified_search_2012_07_16  (cost=23.45..843413.45 rows=22 width=27)
                     Index Cond: (site_id = $45)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 47) OR (hashed SubPlan 48)))
                     SubPlan 47
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 48
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=888167.95..888167.97 rows=1 width=27)
               InitPlan 49 (returns $48)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_17  (cost=23629.66..888165.51 rows=35 width=27)
                     Recheck Cond: (site_id = $48)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 50) OR (hashed SubPlan 51)))
                     ->  Bitmap Index Scan on unified_search_2012_07_17_site_id  (cost=0.00..23606.20 rows=1266758 width=0)
                           Index Cond: (site_id = $48)
                     SubPlan 50
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 51
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=864109.50..864109.52 rows=1 width=27)
               InitPlan 52 (returns $51)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Index Scan using unified_search_2012_07_18_site_id on unified_search_2012_07_18  (cost=23.45..864107.16 rows=21 width=27)
                     Index Cond: (site_id = $51)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 53) OR (hashed SubPlan 54)))
                     SubPlan 53
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 54
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=803272.65..803272.67 rows=1 width=27)
               InitPlan 55 (returns $54)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Index Scan using unified_search_2012_07_19_site_id on unified_search_2012_07_19  (cost=23.45..803270.32 rows=20 width=27)
                     Index Cond: (site_id = $54)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 56) OR (hashed SubPlan 57)))
                     SubPlan 56
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 57
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=813195.59..813195.61 rows=1 width=27)
               InitPlan 58 (returns $57)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_20  (cost=21599.73..813193.26 rows=19 width=27)
                     Recheck Cond: (site_id = $57)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 59) OR (hashed SubPlan 60)))
                     ->  Bitmap Index Scan on unified_search_2012_07_20_site_id  (cost=0.00..21576.28 rows=1161983 width=0)
                           Index Cond: (site_id = $57)
                     SubPlan 59
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 60
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=821661.87..821661.89 rows=1 width=27)
               InitPlan 61 (returns $60)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_21  (cost=22057.01..821659.55 rows=18 width=27)
                     Recheck Cond: (site_id = $60)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 62) OR (hashed SubPlan 63)))
                     ->  Bitmap Index Scan on unified_search_2012_07_21_site_id  (cost=0.00..22033.56 rows=1181883 width=0)
                           Index Cond: (site_id = $60)
                     SubPlan 62
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 63
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=897672.75..897672.77 rows=1 width=27)
               InitPlan 64 (returns $63)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Index Scan using unified_search_2012_07_22_site_id on unified_search_2012_07_22  (cost=23.45..897670.44 rows=17 width=27)
                     Index Cond: (site_id = $63)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 65) OR (hashed SubPlan 66)))
                     SubPlan 65
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 66
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=878573.33..878573.35 rows=1 width=27)
               InitPlan 67 (returns $66)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_23  (cost=39759.24..878570.98 rows=22 width=27)
                     Recheck Cond: (site_id = $66)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 68) OR (hashed SubPlan 69)))
                     ->  Bitmap Index Scan on unified_search_2012_07_23_site_id  (cost=0.00..39735.78 rows=1220904 width=0)
                           Index Cond: (site_id = $66)
                     SubPlan 68
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 69
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=1047637.90..1047637.92 rows=1 width=27)
               InitPlan 70 (returns $69)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_24  (cost=46672.04..1047635.57 rows=19 width=27)
                     Recheck Cond: (site_id = $69)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 71) OR (hashed SubPlan 72)))
                     ->  Bitmap Index Scan on unified_search_2012_07_24_site_id  (cost=0.00..46648.58 rows=1362274 width=0)
                           Index Cond: (site_id = $69)
                     SubPlan 71
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 72
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=802633.89..802633.91 rows=1 width=27)
               InitPlan 73 (returns $72)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Index Scan using unified_search_2012_07_25_site_id on unified_search_2012_07_25  (cost=23.45..802631.61 rows=13 width=27)
                     Index Cond: (site_id = $72)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 74) OR (hashed SubPlan 75)))
                     SubPlan 74
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 75
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=781614.54..781614.56 rows=1 width=27)
               InitPlan 76 (returns $75)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_26  (cost=35048.70..781612.21 rows=20 width=27)
                     Recheck Cond: (site_id = $75)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 77) OR (hashed SubPlan 78)))
                     ->  Bitmap Index Scan on unified_search_2012_07_26_site_id  (cost=0.00..35025.24 rows=1081943 width=0)
                           Index Cond: (site_id = $75)
                     SubPlan 77
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 78
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=1057339.51..1057339.53 rows=1 width=27)
               InitPlan 79 (returns $78)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_27  (cost=47268.26..1057337.12 rows=27 width=27)
                     Recheck Cond: (site_id = $78)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 80) OR (hashed SubPlan 81)))
                     ->  Bitmap Index Scan on unified_search_2012_07_27_site_id  (cost=0.00..47244.80 rows=1460438 width=0)
                           Index Cond: (site_id = $78)
                     SubPlan 80
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 81
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=790017.15..790017.17 rows=1 width=27)
               InitPlan 82 (returns $81)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Bitmap Heap Scan on unified_search_2012_07_28  (cost=49557.68..790014.84 rows=18 width=27)
                     Recheck Cond: (site_id = $81)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 83) OR (hashed SubPlan 84)))
                     ->  Bitmap Index Scan on unified_search_2012_07_28_site_id  (cost=0.00..49534.23 rows=1069099 width=0)
                           Index Cond: (site_id = $81)
                     SubPlan 83
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 84
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
         ->  Aggregate  (cost=775460.21..775460.23 rows=1 width=27)
               InitPlan 85 (returns $84)
                 ->  Limit  (cost=0.00..2.17 rows=1 width=2)
                       ->  Seq Scan on sites  (cost=0.00..2.17 rows=1 width=2)
                             Filter: ((site_desc)::text = 'Germany'::text)
               ->  Index Scan using unified_search_2012_07_29_site_id on unified_search_2012_07_29  (cost=23.45..775457.90 rows=17 width=27)
                     Index Cond: (site_id = $84)
                     Filter: ((qty_sold > 0) AND ((listing_title)::text ~~ '%Wrangler%'::text) AND (condition_rollup = 1) AND ((hashed SubPlan 86) OR (hashed SubPlan 87)))
                     SubPlan 86
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
                     SubPlan 87
                       ->  HashAggregate  (cost=7.36..10.85 rows=349 width=4)
                             ->  Seq Scan on category_11450  (cost=0.00..6.49 rows=349 width=4)
Avatar of BigSchmuh
BigSchmuh
Flag of France image
>>post us some SQL and their query plan
==> Can we although read the SQL ?
Avatar of Sara bhai
Sara bhai
Flag of India image

ASKER

SELECT SUM(SUM_COST) AS SumSoldPrice , SUM(COUNT_Products) AS CountListing , SUM(AVG_SUM_COST) AS AvgPrice
FROM
(
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_01  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_02  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_03  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_04  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_05  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_06  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_07  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_08  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_09  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_10  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_11  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_12  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_13  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_14  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_15  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_16  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_17  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_18  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_19  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_20  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_21  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'      
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_22  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
      
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_23  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

      
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_24  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_25  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_26  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_27  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_28  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_29  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'



) AS JulyMonthTable


/********************************/

SELECT SUM(SUM_COST) AS SumSoldPrice , SUM(COUNT_Products) AS CountListing , SUM(AVG_SUM_COST) AS AvgPrice
FROM
(
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_01  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_02  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_03  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_04  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_05  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_06  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_07  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_08  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_09  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_10  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_11  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_12  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_13  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_14  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_15  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_16  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_17  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_18  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_19  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_20  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_21  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'      
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_22  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
      
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_23  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

      
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_24  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_25  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_26  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_27  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_28  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_29  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  > 0      --Sold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'



) AS JulyMonthTable






/****************************************************************************/


SELECT COALESCE(SUM(SUM_COST),0) AS SumSoldPrice , COALESCE(SUM(COUNT_Products),0) AS CountListing , COALESCE(SUM(AVG_SUM_COST),0) AS AvgPrice
FROM
(
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_01  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_02  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_03  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_04  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_05  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_06  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_07  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_08  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_09  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_10  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_11  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_12  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_13  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_14  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_15  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_16  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_17  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_18  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_19  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_20  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_21  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'      
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_22  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
      
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_23  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

      
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_24  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_25  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_26  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_27  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_28  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_29  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 1  --New--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'



) AS JulyMonthTable

/*******************************************************************************************************************************/




-----------Used--UnSold-------

SELECT COALESCE(SUM(SUM_COST),0) AS SumSoldPrice , COALESCE(SUM(COUNT_Products),0) AS CountListing , COALESCE(SUM(AVG_SUM_COST),0) AS AvgPrice
FROM
(
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_01  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_02  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_03  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_04  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_05  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_06  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_07  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_08  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_09  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_10  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_11  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_12  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_13  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_14  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_15  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_16  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_17  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_18  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_19  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_20  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_21  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'      
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_22  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
      
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_23  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL

      
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_24  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_25  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_26  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_27  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_28  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'
UNION ALL   
       
SELECT 
 SUM((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS SUM_COST
, COUNT(item_id) AS COUNT_Products
, AVG((COALESCE(buy_it_now_price_usd,current_price_usd)* qty_sold )) AS AVG_SUM_COST
FROM unified_search_2012_07_29  
WHERE site_id = (SELECT  site_id FROM sites WHERE site_desc = 'Germany' limit 1)
      AND qty_sold  <= 0     --UnSold--
      AND condition_rollup = 2  --Used--
      AND ( leaf_category_1 IN (SELECT DISTINCT category_id from category_11450)
               OR
               leaf_category_2 IN (SELECT DISTINCT category_id from category_11450)
            )    
      AND listing_title LIKE '%Wrangler%'



) AS JulyMonthTable

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of BigSchmuh
BigSchmuh
Flag of France image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
If you know the categories and periods you wish to group by beforehand, you could use triggers to maintain an accumulator record in a period table.