Home > front end >  How do I count distinct rows with duplicates in a table?
How do I count distinct rows with duplicates in a table?

Time:09-02

I have a problem with my homework and maybe somebody could help me:

I need a select query to have as a result two columns: one named 'Date' with distinct rows (no duplicates) from the column ab_datum and one column called 'Number', that counts the rows from ab_datum.

I've tried this:

>select distinct ab_datum Date, count(distinct ab_datum) Number
from abflug
order by ab_datum;

But of course it didn't work - I've tried some other solutions, but I didn't help.

This is the table I have to work with:

AB_DATUM F_BEZ HERST TYP PER_NR SER_NR AB_ZEIT
06.07.13 LH-888 Boeing B747 9fg-he-ztu8 10010071 11.23
08.10.13 LH-238 Airbus A320 z3et-bwe7 10010072 22.06
13.11.13 LH-341 Boeing B737 ba23-0012 10010001 10.23
14.11.13 LH-358 Boeing B737 ba23-0012 10010001 8.17
13.11.13 LH-553 Boeing B777 xv23-0889 10010002 16.53
15.11.13 LH-421 Boeing B777 xv56-3142 10010002 14.45
17.11.13 LH-789 Airbus A330 45-6789 10010003 8.11
14.11.13 LH-112 Boeing B737 ba23-0034 10010001 8.14
17.11.13 LH-421 Boeing B777 xv23-0889 10010002 16.26
18.11.13 LH-223 Airbus A380 ab-45-6xf 10010004 9.45
19.11.13 LH-634 Airbus A350 5478-awe3 10010005 20.25
18.02.14 LH-238 Airbus A320 z3et-bwe7 10010072 23.06

I would be very grateful for your help - I am total beginner in SQL... Thanks!

CodePudding user response:

If you want to count the rows for each date then you need to aggregate with a group by clause:

select ab_datum as "Date", count(*) as "Number"
from abflug
group by ab_datum
order by ab_datum;

Or if the dates have non-midnight times then then truncate those to midnight:

select trunc(ab_datum) as "Date", count(*) as "Number"
from abflug
group by trunc(ab_datum)
order by trunc(ab_datum);

Both date and number are reserved words, so if you really want those as the column aliases then they have to be quoted identifiers.

CodePudding user response:

The first thing that comes to my mind is something like this:

SELECT ab_datum AS "Date", COUNT(*) AS "Number"
FROM abflug
GROUP BY ab_datum
ORDER BY BY ab_datum;

I dont know if this is what you were looking for. If not, please provide more information about your desired result.

  • Related