1

I need your help!

Let's say I have this table:

Instance Date MetricID Value
Marc 09/14/21 1 5
Marc 09/14/21 2 2
Marc 09/14/21 3 1
John 09/14/21 1 10
John 09/14/21 2 1
John 09/14/21 3 1
Marc 09/15/21 1 15
Marc 09/15/21 2 0
Marc 09/15/21 3 1
John 09/15/21 1 10
John 09/15/21 2 1
John 09/15/21 3 0

And I want this:

Instance LatestDateMetric1 LatestDateMetric2 LatestDateMetric3
Marc 09/15/21 09/14/21 09/15/21
John 09/15/21 09/15/21 09/14/21

I tried this code, It looks a bit like I want except It takes the value even if it is null and the result is by line not column.

 SELECT "Instance", "MetricID", MAX("Date") as "LatestDate"
FROM "API_Metric2"
GROUP BY "Instance", "MetricID"

This is the result I got:

Instance MetricID LatestDate
Marc 1 09/15/21
Marc 2 09/15/21
Marc 3 09/15/21
John 1 09/15/21
John 2 09/15/21
John 3 09/15/21

And I also tried this:

SELECT "Instance", 
            CASE WHEN "MetricID"=1 AND "Value" NOT NULL THEN MAX("Date") ELSE 0 END) AS "LatestDateMetric1",
            CASE WHEN "MetricID"=2 AND "Value" NOT NULL THEN MAX("Date") ELSE 0 END) AS "LatestDateMetric2",
            CASE WHEN "MetricID"=3 AND "Value" NOT NULL THEN MAX("Date") ELSE 0 END) AS "LatestDateMetric3"
    FROM "StackExemple"
    GROUP BY "Instance", "Date", "MetricID"

But I get this error message:

Parse error at line 2, column 66. Encountered: "Value"

Edit: I also got this code which seems to be working but It's not taking the null values into account. It only display 09/15/21 as the LatestDate for all metrics.

SELECT "InstanceName",
                    MAX(CASE WHEN "MetricID" = 4 THEN "Date" END) as "LatestProjectCreated",
                    MAX(CASE WHEN "MetricID" = 5 THEN "Date" END) as "LatestActionCreated",
                    MAX(CASE WHEN "MetricID" = 8 THEN "Date" END) as "LatestUserCreated"
FROM "API_InstanceMetric"
GROUP BY "InstanceName";

Edit2: The issue persists even with adding the "Value IS NOT NULL" as below

SELECT "Instance",
                    MAX(CASE WHEN "MetricID" = 1  AND "Value" IS NOT NULL THEN "Date" END) as "LatestProjectCreated",
                    MAX(CASE WHEN "MetricID" = 2 AND "Value" IS NOT NULL THEN "Date" END) as "LatestActionCreated",
                    MAX(CASE WHEN "MetricID" = 3 AND "Value" IS NOT NULL THEN "Date" END) as "LatestUserCreated" FROM "StackExemple" GROUP BY "Instance";
6
  • 1
    Hi, and welcome to dba.se! Do you want SQL Server or MySQL? Please delete as appropriate! Also, please put your table structure and data on dbfiddle.uk. It's x IS NOT NULL and not x NOT NULL in your SQL! Also, if you can, you should dispense with the quoted identifiers - you don't need them - you have no spaces or special characters (i.e. ^[A-Za-z0-9_]). And you don't want the values? Sep 15 at 11:21
  • @Vérace Thanks for your comment! I'm using Zoho Analytics so both SQL Server and MySQL are fine. I only need to check if the values are positive but I don't need to aggregate or manipulate them Sep 15 at 11:29
  • Almost forgot - please always include the version(s) of your server(s). MySQL 8 in particular introduced a lot of new functionality - including (critically for you) CHECK constraints - you just put CHECK (value > 0) and you're done as far as checking goes - for earlier versions of MySQL, you have to use triggers!. Don't forget the fiddle - with any SQL you tried! Sep 15 at 11:33
  • Thank you for taking the time to help me, It seems I don't have MySQL8 since the Check clause does not appear. I tried the "Value is not null" but it still returns "09/15/21" for all the metrics :( Sep 15 at 12:10
  • Can you show the results for your working query? It might be good for other users - or even yourself in the future - you'd be amazed how often, if you solve a problem once, it crops up again 6 months/1 year... later and you're going "I've seen this, where did I put the solution?" - if it's up here, you'll always be able to find it! Sep 15 at 12:42
0

I got the answer from StackOverflow:

SELECT "Instance",
       MAX(CASE WHEN "MetricID" = 1 THEN "Date" END) as LatestDateMetric1,
       MAX(CASE WHEN "MetricID" = 2 THEN "Date" END) as LatestDateMetric2,
       MAX(CASE WHEN "MetricID" = 3 THEN "Date" END) as LatestDateMetric3
FROM "API_Metric2"
WHERE "Value" > 0
GROUP BY "Instance";

I just needed to add a WHERE clause smh

The result looks exactly like what I expected:

Instance LatestDateMetric1 LatestDateMetric2 LatestDateMetric3
Marc 09/15/21 09/14/21 09/15/21
John 09/15/21 09/15/21 09/14/21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.