0

I have a database of my bike rides that I am using to learn SQL using Libreoffice Base and HSQL. The database consists of a table with columns for: Index, Date, Bike, Miles.

What I would like to do is create a column with sums of the bike rides for each year for my road bikes and another for my mtn bikes. I started with a subquery sum, but this gives me a total for the entire database. Here is my code for the road bikes, another subquery would be used for the mtn bikes column:

SELECT YEAR( "RideDate" ) AS "Year", SUM( "Miles" ) AS "Miles", 
( SELECT SUM( "Miles" ) FROM "BikeDate" 
   WHERE( "Bike" = 'Fuji' OR "Bike" = 'Yfoil' )) AS "% Miles" 
FROM "BikeDate" AS "BikeDate" 
GROUP BY YEAR( "RideDate" ) 
ORDER BY YEAR( "RideDate" ) DESC

Since I am grouping by year, how do I obtain the sum for each grouping element->year? I apologize for not knowing the correct terminology. I also don't want to do a plain sum where I group by year and bike, I have that. I want to break the year sum apart using the where clause. Any help is appreciated, Dave. LOBase 4.2.3.3 .

user50506
  • 181
  • 1
  • 7

1 Answers1

0

Let me suggest two possibilities, depending on what you really need, in order to return the values as in your original post:

SELECT 
   YEAR( "RideDate" ) AS "Year", 
   SUM( "Miles" ) AS "Miles", 
   SUM(CASE WHEN "Bike" = 'Fuji' OR "Bike" = 'Yfoil' THEN "Miles" ELSE 0 END) as "% Miles"
FROM "BikeDate" AS "BikeDate" 
GROUP BY YEAR( "RideDate" ) 
ORDER BY YEAR( "RideDate" ) DESC

In order to create an additional column, that really calculates the Percent . . . something like:

SELECT 
   YEAR( "RideDate" ) AS "Year", 
   SUM( "Miles" ) AS "Miles", 
   SUM(CASE WHEN "Bike" = 'Fuji' OR "Bike" = 'Yfoil' THEN "Miles" ELSE 0 END) as "Miles_Fuji_Yfoil",
   SUM(CASE WHEN "Bike" = 'Fuji' OR "Bike" = 'Yfoil' THEN "Miles" ELSE 0 END) * 100.0000000 / SUM("Miles") as "Pct_Miles_Fuji_Yfoil"
FROM "BikeDate" AS "BikeDate" 
GROUP BY YEAR( "RideDate" ) 
ORDER BY YEAR( "RideDate" ) DESC

Explanation: This uses the HSQL function CASE WHEN . . . OR . . . value ELSE 0 END so it will only SUM ( Aggregate function ) those values.

Sliderule
  • 31
  • 2