0

I have one table named FDR_DATA. There are following fields present in the table. Fields are: B1_NAME, B2_NAME, B3_NAME, ELEMENT, DATUM, WERT. Now I want to query the total number of data in this table and following output will generate.

sql> select count (*) from fdr_data where datum like (select sysdate -1 from dual);

(It only shows the number of counts) But I want to print the datum (date) field beside the count.

DATUM COUNT
04.05.2016 899019

How to write down the query to get the output like this way?

I have tried,
sql> select DATUM, count (*) from fdr_data where datum like (select sysdate -1 from dual)
But couldnot get the desired output.

0

sql> select count (*) as fdr_data from fdr_data where datum like (select sysdate -1 from dual);

Output:

fdr_data    
-------- 
899019    
-1

I don't know if any SQL dialects offer other ways, but as you haven't said what SQL server you use, but this should work:

SELECT datum,count(*) FROM fdr_data WHERE datum LIKE (SELECT sysdate-1 FROM dual) GROUP BY datum;

When the WHERE clause means there is only one row in the result it can feel strange having to add a GROUP BY clause, but the parser will not know so we add it to please that.

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.