1

I need some help by writing a SQL statement.

I have a table (it's already a result from another query) with 4 columns e.g.:

Firstname | Lastname | Softwarename | Hours

With a example data set like:

Row1: Max | Huber | Word | 25h

Row2: Max | Huber | Excel | 12h

The result of the query should be the first row. My problem is that I am forced to add the Softwarename to the group by, but when I do that the result is obviously incorrect.

How do I do that?

Thank you!

3
  • ORDER BY Hours DESC LIMIT 1
    – Akina
    Nov 1 '19 at 16:50
  • Please consider reading this advice
    – mustaccio
    Nov 1 '19 at 16:53
  • Just a final tip - when you add additional information, you should put it into the question rather than adding it as comments - that way people here can get all the information quickly in just one place rather than having to read through the comments. There are a few articles on how to ask questions here on my profile - you might want to take a look?
    – Vérace
    Nov 1 '19 at 17:31
0

If I've understood you (at last) correctly, I think you want something like this (see fiddle here):

Create your table:

CREATE TABLE test_tab
(
  first_name VARCHAR (25) NOT NULL,
  last_name  VARCHAR (25) NOT NULL,
  soft_name  VARCHAR (25) NOT NULL,
  soft_hours VARCHAR (25) NOT NULL
);

Populate it:

INSERT INTO test_tab VALUES ('Max', 'Huber', 'Word',  '25h');
INSERT INTO test_tab VALUES ('Max', 'Huber', 'Excel', '15h');

INSERT INTO test_tab VALUES ('Felix', 'TheCat', 'Outlook', '35h');
INSERT INTO test_tab VALUES ('Felix', 'TheCat', 'Word',  '25h');
INSERT INTO test_tab VALUES ('Felix', 'TheCat', 'Excel', '15h');

INSERT INTO test_tab VALUES ('Donald', 'Duck', 'Excel', '20h');
INSERT INTO test_tab VALUES ('Donald', 'Duck', 'Word',  '8h');
INSERT INTO test_tab VALUES ('Donald', 'Duck', 'Outlook', '3h');

And use the following premliminary SQL (included to demonstrate the thought processes that went into formulating the answer. I find that people learn better if they can see a "breadcrumb trail" (cf. Hansel and Gretel)):

SELECT
  first_name,
  last_name,
  MAX(CAST(soft_hours AS UNSIGNED)) AS use_duration
FROM test_tab
GROUP BY first_name, last_name
ORDER BY last_name;

The CAST is necessary because of the inclusion of the hin the string for the duration of usage (much better to use a plain INTEGER). If the CAST isn't present, 8h will be "greater than" 20h for Donald Duck when sorted as a character string - CASTing to UNSIGNED removes that problem.

Result:

first_name  last_name   use_duration
    Donald       Duck             20
       Max      Huber             25
     Felix     TheCat             35

Having obtained the first and last names and the use_duration, we are now in a position to JOIN back to our original table this way:

SELECT 
  t1.first_name, t1.last_name, t1.soft_name, 
  CAST(t1.soft_hours AS UNSIGNED) AS "usage"
FROM test_tab t1
JOIN
(
  SELECT 
    first_name,
    last_name,
    MAX(CAST(soft_hours AS UNSIGNED)) AS use_duration
  FROM test_tab
  GROUP BY first_name, last_name
) AS t2
ON t1.first_name = t2.first_name AND t1.last_name = t2.last_name
AND CAST(t1.soft_hours AS UNSIGNED) = t2.use_duration
ORDER BY t1.last_name;

Result:

first_name  last_name   soft_name   usage
    Donald       Duck       Excel      20
       Max      Huber        Word      25
     Felix     TheCat     Outlook      35

Now, I would recommend that you keep the duration as just a pure INTEGER rather than use 25h. I hope this responds to your needs, - if not, then please expand on your explanation and I'll endeavour to revise my answer to your satisfaction. p.s. welcome to the forum! :-)

5
  • Thank you for fast response. The problem is, the MySQL version which I am using enforces me to add all the columns which I select to the group by or use it with an aggregate function. The full error is: "ERROR: column "tmp1.sw_name" must appear in the GROUP BY clause or be used in an aggregate function Position: 22". Thank you :) Nov 1 '19 at 14:34
  • Ok thank you. I wasn't precise enough, for example when you have another tuple with e.g. Felix | Test | Powerpoint |30, the result should not only be Felix | Test | 30, rather each person should be the result with it's maximum number of hours. So the correct result would be Max Huber Word 25 and Felix Test 30. Sorry for not being precise enough! Nov 1 '19 at 15:00
  • No the target is to print out each user the most used software. If a user uses excel or word or something like that doesn't really matter. I just have to show the software name. The important thing is to get only one entry for each user. Nov 1 '19 at 15:15
  • THANK YOU! Sorry for not being precise enough. You helped me a lot :) Nov 1 '19 at 17:01
  • Done - and added an ORDER BY to boot - best practice is for every query to have an ORDER BY. To the OP, if you're using (or can upgrade to) MySQL 8, you have much more choice of how to do this - Window (Analytic) functions and CTE's (Common Table Expressions). It's well worth getting to know how to use these!
    – Vérace
    Nov 1 '19 at 17:22

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.