CODEWITHSUNDEEP
sql
John Walker
John Walker

Reputation: 1121

Distinct inside a CASE WHEN SQL

SELECT
    SUM(NormalCount) NormalCount,
    SUM(DiscountCount) DiscountCount,
    SUM(TotalAmountNormal) TotalAmountNormal,
    SUM(TotalAmountDiscounted) TotalAmountDiscounted,
    COUNT(Trip_No) TotalTrip
  FROM
  (

  SELECT
    CASE WHEN PriceType_ID=0 THEN 1 ELSE 0 END NormalCount,
    CASE WHEN PriceType_ID<>0 THEN 1 ELSE 0 END DiscountCount,
    CASE WHEN PriceType_ID=0 THEN Amount END TotalAmountNormal,
    CASE WHEN PriceType_ID<>0 THEN Amount ELSE 0 END TotalAmountDiscounted,
    DISTINCT(Trip_No) //here error

    FROM "Tbl_Ticket"
    WHERE strftime('%Y-%m-%d', datetime('now'), 'localtime') = strftime('%Y-%m-%d', Ticket_Date)
    AND Vehicle_ID=1
    AND Driver_ID=1
    AND Route_ID=93
    AND Ticket_Date BETWEEN strftime('%Y-%m-%d %H:%M:%S', "2013-12-04 00:21:07") AND strftime('%Y-%m-%d %H:%M:%S', "2013-12-04 00:50:50")
 ) t

i want to Distinct Trip_No and count how many the trip_no have, any idea?

Upvotes: 0

Views: 327

Answers (2)

Allan
Allan

Reputation: 17429

Distinct isn't a function, it's a keyword that can be used in a couple of ways. If can be used after select to tell the database to return only unique rows or it can be used within an aggregate function (i.e. min, max, count, etc.) to tell that function that it should be applied only to the set of unique values for that field. In the former case, it can be used in just about any query, but in the latter case (such as yours) the query must have a group by clause.

As had previously been suggested, it seems like the simplest solution for you case is to move the distinct keyword to within the count function:

SELECT
    SUM(NormalCount) NormalCount,
    SUM(DiscountCount) DiscountCount,
    SUM(TotalAmountNormal) TotalAmountNormal,
    SUM(TotalAmountDiscounted) TotalAmountDiscounted,
    COUNT(DISTINCT Trip_No) TotalTrip
  FROM( 
    SELECT
      CASE WHEN PriceType_ID=0 THEN 1 ELSE 0 END NormalCount,
      CASE WHEN PriceType_ID<>0 THEN 1 ELSE 0 END DiscountCount,
      CASE WHEN PriceType_ID=0 THEN Amount END TotalAmountNormal,
      CASE WHEN PriceType_ID<>0 THEN Amount ELSE 0 END TotalAmountDiscounted,
      trip_no
    FROM "Tbl_Ticket"
    WHERE strftime('%Y-%m-%d', datetime('now'), 'localtime') = strftime('%Y-%m-%d', Ticket_Date)
      AND Vehicle_ID=1
      AND Driver_ID=1
      AND Route_ID=93
      AND Ticket_Date BETWEEN strftime('%Y-%m-%d %H:%M:%S', "2013-12-04 00:21:07") AND strftime('%Y-%m-%d %H:%M:%S', "2013-12-04 00:50:50")
  ) t

This solution will provide a single result row which will summarize the entire table. If you want one row per trip_no, @Gordon Linoff's answer is appropriate.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271211

I think the query you are looking for is an aggregation query:

SELECT sum(CASE WHEN PriceType_ID=0 THEN 1 ELSE 0 END) as NormalCount,
       sum(CASE WHEN PriceType_ID<>0 THEN 1 ELSE 0 END) as DiscountCount,
       sum(CASE WHEN PriceType_ID=0 THEN Amount END) as TotalAmountNormal,
       sum(CASE WHEN PriceType_ID<>0 THEN Amount ELSE 0 END) as TotalAmountDiscounted,
       Trip_No
FROM "Tbl_Ticket"
WHERE strftime('%Y-%m-%d', datetime('now'), 'localtime') = strftime('%Y-%m-%d', Ticket_Date)
      AND Vehicle_ID=1
      AND Driver_ID=1
      AND Route_ID=93
      AND Ticket_Date BETWEEN strftime('%Y-%m-%d %H:%M:%S', "2013-12-04 00:21:07") AND strftime('%Y-%m-%d %H:%M:%S', "2013-12-04 00:50:50")
group by trip_no

Upvotes: 4

Related Questions