CODEWITHSUNDEEP
mysql
Test
Test

Reputation: 549

Generate a RollUp sql query

I have a problem. Is my SQL query right?

Note: Yes, I know the Join isn't "the best" (my teacher said we should use this and nothing else), but it should be with this join so in the case e.g. g.geoID = s.geoID is right.

enter image description here

The following output is generated by a single SQL statement:

enter image description here

These values hold for the productGroup "camcorder" in cities in Germany.

SELECT Geograhpie.Cities, Time.Years, AVG(Sales.Turnover) as AverageTurnover
FROM Gerographie as g, Sales as s, Time as t, Products as p
WHERE g.geoID = s.geoID
AND s.timeID = t.timeID
AND s.ProductID=p.ProductID
And Product.Productyfamily = "camcorder"
AND g.country = "Germany"
GROUP BY cities, years WITH ROLLUP;
ORDER BY citiesIS NULL, years IS NULL;

Upvotes: 0

Views: 37

Answers (1)

Marco
Marco

Reputation: 1232

Your query is almost good, for some reason you are putting a part that is just not right, and even if it was inside will make the rollup fail.

SELECT   geograhpie.cities,
         time.years,
         Avg(sales.turnover) AS AverageTurnover
FROM     gerographie         AS g,
         sales               AS s,
         time                AS t,
         products            AS p
WHERE    g.geoid = s.geoid
AND      s.timeid = t.timeid
AND      s.productid=p.productid
AND      product.productyfamily = "camcorder"
AND      g.country = "germany"
GROUP BY cities, years WITH rollup;

WITH ROLLUP cannot be used with ORDER BY. Some sorting is still possible by using ASC or DESC clauses with the GROUP BY column, although the super-aggregate rows will always be added last.

Upvotes: 1

Related Questions