SQL Query Multiple dates to one date

Question

I have a table psttodo-uit with some fields like Hostess Code, Datum Bezoek 1, PA, PB, PG, GoedkeuringDoorNew, Blanco, .. .

Now I would like to select all the fields where Hostess Code is equal to ... . I want an overview like this:

1 march  |   info     info     info
2 march  |   info     info     info

But in my table I have :

2014-04-03 11:32:18
2014-04-03 11:22:16
2014-04-02 16:05:22
2014-04-02 15:40:43
2014-04-02 15:17:41

So I would like to select for each day and make a count of the other fields like count(PA = 1). Can I do this in one SQL Query?

The question is not very clear: Do you want to group lines by day and count the rows for each day? if so try SELECT day(`Datum Bozek`),month(`Datum bozek`),year(`Datum bozek`),count(*) WHERE `Hostess Code`='your hostess code'` group by day(`Datum Bozek`),month(`Datum bozek`),year(`Datum bozek`). for moreinfos see dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html and w3resource.com/mysql/aggregate-functions-and-grouping/… — Antonio Ragagnin
– Antonio Ragagnin, Commented Apr 4, 2014 at 15:32

Thiago Melo · Accepted Answer · 2014-04-04 15:31:58Z

2

Your select has to be something like:

select `Hostess Code`, DATE_FORMAT(`Datum Bezoek 1`, '%e %M'), count(PA), count(PB), count(PG), GoedkeuringDoorNew, Blanco,... where ... group by DATE_FORMAT(`Datum Bezoek 1`, '%e %M') order by DATE_FORMAT(`Datum Bezoek 1`, '%e %M')

I hope it helps.

answered Apr 4, 2014 at 15:31

Thiago Melo

1,1871 gold badge15 silver badges32 bronze badges

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

Comments

SoulTrain · Accepted Answer · 2014-04-04 15:31:52Z

0

Something like:

SELECT DATE_FORMAT(DT, '%e %M')
, COUNT(PA) PA_COUNT
, COUNT(PB) PB_CCOUNT
...
FROM 
YOUR_TABLE
GROUP BY DATE_FORMAT(DT, '%e %M')

answered Apr 4, 2014 at 15:31

SoulTrain

1,9041 gold badge12 silver badges11 bronze badges

Comments

Tiny Haitian · Accepted Answer · 2014-04-04 15:33:51Z

0

Try this:

 Select DATE(date_column), col1, col2 
 from psttodo-uit 
 group by DATE(date_column)
 order by DATE(date_column)

answered Apr 4, 2014 at 15:33

Tiny Haitian

4792 silver badges10 bronze badges

Comments

attila · Accepted Answer · 2014-04-04 15:38:23Z

0

You can do this by grouping the dates using the DATE() function and then count the conditions on the other fields using a sum(case when condition then 1 else 0 end), like this

SELECT DATE(`Datum Bezoek 1`) as MyDate, sum(case when PA=1 then 1 else 0 end) as PA1, count(case when PB=2 then 1 else 0 end) as PB2, other fields here...
FROM yourtablename
WHERE `Hostess Code`=value
GROUP BY DATE(`Datum Bezoek 1`)

answered Apr 4, 2014 at 15:38

attila

2,2291 gold badge13 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL Query Multiple dates to one date

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related