sql query to fetch datewise count from table

Question

Table name = Master

I need an sql query which fetches a datewise result from table.

Data is present in my database as displayed below.

 date                   copiesbooked             surveycount
13-03-2015                  700                      raj
13-03-2015                  700                      raj
13-03-2015                  700                      raj
13-03-2015                  700                      raj

13-03-2015                  701                      raj
13-03-2015                  701                      raj
13-03-2015                  701                      raj
13-03-2015                  701                      raj

13-03-2015                  702                      akash
13-03-2015                  702                      akash
13-03-2015                  702                      akash
13-03-2015                  702                      akash

14-03-2015                  703                      sunil
14-03-2015                  703                     sunil
14-03-2015                  703                      sunil
14-03-2015                  703                      sunil

15-03-2015                  704                      raj
15-03-2015                  704                      raj
15-03-2015                  704                      raj
15-03-2015                  704                      raj

In the above table, there are 3 copies booked on 13-03-2015 named 700,701,702 and only 2 surveyor worked raj and akash....hence surveycount is 2.

Again, there are 1 copiesbooked on 14-03-2015 named 703 and only 1 surveyor worked sunil ...hence surveycount is 1...like wise...

I need output like below

 date            copiesbooked             surveycount
13-03-2015            3                       2
14-03-2015            1                       1
15-03-2015            1                       1

Could you please help to make the sql query for getting such output...

below is my query which i tried...but this does not get distinct count of copiesbooked and surveycount column..my query gets all rows group by date..

select date,
count(copiesbooked) as copiesbooked,
count(surveycount) as  surveycount from master 
group by date

Where is your attempt?

mmmmmpie
– mmmmmpie

2015-03-16 12:15:38 +00:00
Commented Mar 16, 2015 at 12:15 — mmmmmpie
– mmmmmpie, Commented Mar 16, 2015 at 12:15
plz seee...i post my query..and try to help ...me..

sunil satpute
– sunil satpute

2015-03-16 12:18:59 +00:00
Commented Mar 16, 2015 at 12:18 — sunil satpute
– sunil satpute, Commented Mar 16, 2015 at 12:18

Brave Soul · Accepted Answer · 2015-03-16 12:11:42Z

3

group by the date column is what you want. use Distinct to show unique count of entries

select date,
count(distinct copiesbooked) as copiesbooked,
count(distinct surveycount) as  surveycount from master 
group by date

edited Mar 16, 2015 at 12:11

answered Mar 16, 2015 at 12:00

Brave Soul

3,6504 gold badges33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sunil satpute Over a year ago

but my copiesbooked column and surveycount column has same data multiple times....like above table i shown in my question....so your query get count of all rows date wise.....

sunil satpute Over a year ago

plz see my data in my question and my output table......bcoz in my table same data occur multiple times ...i need date wise but distinct count of all column

Brave Soul Over a year ago

@sunilsatpute see my edit . just add distinct keyword to count then it will give you unique count of entries

Collectives™ on Stack Overflow

sql query to fetch datewise count from table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related