1

Here is my code in SQL Server:

--explore rides by month  
SELECT DATEPART(mm,started_at) month  
    ,rider_type
    ,COUNT(ride_id) cnt_ride_id  
FROM trips  
WHERE DATEDIFF(mi,started_at,ended_at) BETWEEN 1 AND 120  
GROUP BY DATEPART(mm,started_at), rider_type  
ORDER BY month

Here are the results:

sample output code

I'd prefer the rider_type columns to be side-by-side and not stacked in the results.

matthewmateomatias
  • 21
  • 4
  • 1
    As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Aug 19 '22 at 23:08
  • 1
    You're looking for `PIVOT` which you can probably do with your data using `CASE` e.g. https://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when – Dale K Aug 19 '22 at 23:10
  • @DaleK Sorry, this is my first question. Thanks for letting me know. – matthewmateomatias Aug 19 '22 at 23:12
  • @DaleK thank you for responding to my question! – matthewmateomatias Aug 19 '22 at 23:13
  • 1
    Now that you know you can remove your image and replace it with formatted text. – Dale K Aug 19 '22 at 23:32
  • 1
    And please show desired results in addition to sample data. – Dale K Aug 20 '22 at 01:09

1 Answers1

0

You need a pivot. This is easier done with conditional aggregation, rather than the specific PIVOT keyword which is more limited.

SELECT
  DATEPART(mm,started_at) month,
  casual = COUNT(CASE WHEN rider_type = 'casual' THEN 1 END),
  member = COUNT(CASE WHEN rider_type = 'member' THEN 1 END)
FROM trips t
WHERE DATEDIFF(mi, started_at, ended_at) BETWEEN 1 AND 120  
GROUP BY DATEPART(mm, started_at)
ORDER BY month

As a side note: you may want to group by EOMONTH( rather than DATEPART(mm

Charlieface
  • 52,284
  • 6
  • 19
  • 43