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.