How do I count distinct rows with duplicates in a table?-CodePudding

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.