sql query question

Question

Have an issue with this query. The records are returned but not grouped the via product_id which is what I want. If I remove the other fields from group by option then I get an error field not contained in the group by or aggregate function.

Thanks for helping

 SELECT  
   o.Product_ID, i.producttitle, i.URLimage,i.price,
   i.customfield2, i.season, i.active, i.discontinued, 
   i.opttitle1, i.opttitle2, i.opttitle3,
      SUM(CASE WHEN processdate BETWEEN '2009-08-01 00:00:00.000' 
              AND '2009-10-31 23:59:00.000' 
           THEN o.sprice ELSE 0 END ) AS totalprice_date1,
      SUM(CASE WHEN processdate BETWEEN '2009-11-01 00:00:00.000' 
              AND '2009-12-31 23:59:00.000' 
           THEN o.sprice ELSE 0 END ) AS totalprice_date2,
      SUM(CASE WHEN processdate BETWEEN '2009-08-01 00:00:00.000'  
              AND '2009-10-31 23:59:00.000' 
           THEN o.Qty  ELSE 0 END ) AS qtysold_date1,
      SUM(CASE WHEN processdate BETWEEN '2009-11-01 00:00:00.000' 
              AND '2009-12-31 23:59:00.000' 
           THEN o.Qty  ELSE 0 END ) AS qtysold_date2
  FROM  dbo.getskusold o
       inner join imagereport i
          on o.product_id = i.product_id 
  GROUP BY o.Product_ID, i.producttitle, i.URLimage, 
       i.price, i.customfield2, i.season, 
       i.active, i.discontinued, i.opttitle1, 
       i.opttitle2, i.opttitle3

Answer 1

If this is SQL Server, the grouping is based on all the fields listed in the group by clause, so if any of the other fields for the same Product_ID are different, they will be classed as separate rows.

You either stop outputting the fields that are different, or choose to aggregate them and specify which of the multiple values you want (min, max etc) for when there are duplicates.

Answer 2

If you want the results grouped by only Product_ID, remove the other field names from your GROUP BY clause. If you do this, you will also need to remove the same field names from your SELECT list (or surround the field names with one an aggregate function eg. MAX, MIN, SUM, AVG). If you still need those columns returned, you can do another JOIN to the source table for those columns to your query as a nested subquery.

Answer 3

Since you're not doing any aggregation group by is just an indirect way of controlling order (and it's not reliable at that, as that's not what it's meant for).

If you only want to return a single product ID you must either aggregate on the other fields, or add a clause to your where statement which tells the database EXACTLY which single record you want to return.

Answer 4

I think this is what you want... if not then you have to explain more about the expected output. NOTE in ms server 2005+ it is faster to do a CTE to get the prodid totals and then do the link to the imagereport table. -- or use a temporary table.

SELECT getskusold.Product_ID, 
  max(imagereport.producttitle),
  max(imagereport.URLimage),
  max(imagereport.price),
  max(imagereport.customfield2),
  max(imagereport.season),
  max(imagereport.active),
  max(imagereport.discontinued),
  max(imagereport.opttitle1),
  max(imagereport.opttitle2),
  max(imagereport.opttitle3),

SUM(CASE WHEN processdate BETWEEN '2009-08-01 00:00:00.000' AND '2009-10-31 23:59:00.000' THEN getskusold.sprice ELSE 0 END ) AS totalprice_date1,
SUM(CASE WHEN processdate BETWEEN '2009-11-01 00:00:00.000' AND '2009-12-31 23:59:00.000' THEN getskusold.sprice ELSE 0 END ) AS totalprice_date2,
SUM(CASE WHEN processdate BETWEEN '2009-08-01 00:00:00.000' AND '2009-10-31 23:59:00.000' THEN getskusold.Qty  ELSE 0 END ) AS qtysold_date1,
SUM(CASE WHEN processdate BETWEEN '2009-11-01 00:00:00.000' AND '2009-12-31 23:59:00.000' THEN getskusold.Qty  ELSE 0 END ) AS qtysold_date2

FROM  dbo.getskusold
inner join imagereport
on getskusold.product_id = imagereport.product_id 

GROUP BY getskusold.Product_ID

Answer 5

You are 'Grouping by' all the fields in the group by clause, If you want to group onl;y by the product Id, then that's the only one that should be in the Group By clause. Then all the other columns in the Select need to be output using some aggregate function (line Min, Max, Avg, etc... )

Try this:

 SELECT o.Product_ID, 
   Min(i.producttitle) MinTitle, Min(i.URLimage) MinImage, 
   Min(i.price) MinPrice, Min(i.customfield2) MinCustom, 
   Min(i.season) MinSeaason, Min(i.active) MinActive, 
   Min(i.discontinued) MinDiscontinued, 
   Min(i.opttitle1) MinOptTitle1, Min(i.opttitle2) MinOptTitle2, 
   Min(i.opttitle3)  MinOptTitle3,
      SUM(CASE WHEN processdate BETWEEN '2009-08-01 00:00:00.000' 
              AND '2009-10-31 23:59:00.000' 
           THEN o.sprice ELSE 0 END ) AS totalprice_date1,
      SUM(CASE WHEN processdate BETWEEN '2009-11-01 00:00:00.000' 
              AND '2009-12-31 23:59:00.000' 
           THEN o.sprice ELSE 0 END ) AS totalprice_date2,
      SUM(CASE WHEN processdate BETWEEN '2009-08-01 00:00:00.000'  
              AND '2009-10-31 23:59:00.000' 
           THEN o.Qty  ELSE 0 END ) AS qtysold_date1,
      SUM(CASE WHEN processdate BETWEEN '2009-11-01 00:00:00.000' 
              AND '2009-12-31 23:59:00.000' 
           THEN o.Qty  ELSE 0 END ) AS qtysold_date2
  FROM  dbo.getskusold o
       inner join imagereport i
          on o.product_id = i.product_id 
  GROUP BY o.Product_ID

or, better, (cause it's clearer), generate aggregate resultset first, then join to image table:

 Select z.ProductId, i.producttitle, 
    i.URLimage, i.price, i.customfield2, 
    i.season, i.active, i.discontinued,       
    i.opttitle1, i.opttitle2, i.opttitle3,
    z.totalprice_date1, z.qtysold_date1,
    z.totalprice_date2, z.qtysold_date2
 From 
    (Select Product_ID ProductId, 
        Sum(CASE WHEN processdate BETWEEN '2009-08-01 00:00:00.000' 
              AND '2009-10-31 23:59:00.000' 
           THEN sprice ELSE 0 END ) AS totalprice_date1,
        Sum(CASE WHEN processdate BETWEEN '2009-11-01 00:00:00.000' 
              AND '2009-12-31 23:59:00.000' 
           THEN sprice ELSE 0 END ) AS totalprice_date2,
        Sum(CASE WHEN processdate BETWEEN '2009-08-01 00:00:00.000'  
              AND '2009-10-31 23:59:00.000' 
           THEN Qty  ELSE 0 END ) AS qtysold_date1,
        Sum(CASE WHEN processdate BETWEEN '2009-11-01 00:00:00.000' 
              AND '2009-12-31 23:59:00.000' 
           THEN Qty  ELSE 0 END ) AS qtysold_date2
     From dbo.getskusold
     Group By Product_ID) Z
       Join imagereport i
          on i.product_id = Z.Productid

Answer 6

From my experience there are many rows that tie to a particular product Id and the query wouldn't know which row to return, but i have a partial solution.

Take a look at this:

select (select top 1 [Name] from Product), SupplierID
from Product
group by SupplierID

SupplierID is the foreign key and has multiple values. Im asking the sql engine to select the first row of every group. I works this way. If you want to concatenate the rows, check this out.