Want to count the number of entries based on the date

[shivanaikrachanaikar]

Hi,

As i have table with Date (data type as character) but i want to count the number of
entries of that date based on the use_id.

table is like this
User id::UserName::Today_date::Rate::Amount::
1011:: shiva:: 03/05/2017 100 120
1012 raj 03/05/2017 50 150
1011 shiva 03/05/2017 60 140
1011 shiva 04/05/2017 10 14
1011 shiva 04/05/2017 45 142

I want to count the userid based on the date

like output should be
1011–>Count 2;
1012–>count 1

[DaveMaxwell]

I’m not sure what database you’re using, but GROUP BY is what you need, and the syntax is basically the same. The group by is the critical piece and in MOST dbms, the fields on the group by line MUST be included in the select portion of the statement.

SELECT field1
     , COUNT(*)
  FROM tableName
 WHERE condition = value
 GROUP BY field1

Since it’s better to learn by trying, try to see if you can get the syntax down for your table structure, and if you have issues, please post back with what you’ve come up with and we can help you further.

[r937]

@DaveMaxwell

The group by is the critical piece and in MOST dbms, the fields on the group by line MUST be included in the select portion of the statement.

not to pick on you, because you help so many others, but this isn’t quite accurate enough

what you described could be illustrated by this –

SELECT column1 , SUM(foo) FROM ... GROUP BY column1 , column2
which is perfectly legal, syntactically, in all databases, if not semantically useful

what you were thinking of was this situation –

SELECT column1 , column2 , SUM(foo) FROM ... GROUP BY column1
which definitely fails syntactically in many databases… and because mysql happily runs it, has caused much anguish to mysql developers

look at it again, and i hope you’ll see that “the fields on the group by line MUST be included in the select portion of the statement” actually describes my first scenario, which is fine

[DaveMaxwell]

@r937

not to pick on you, because you help so many others, but this isn’t quite accurate enough

@r937

look at it again, and i hope you’ll see that “the fields on the group by line MUST be included in the select portion of the statement” actually describes my first scenario, which is fine

doh! I hate when you catch me in stupid mistakes…

Thanks for the clarification, though…

@r937

what you described could be illustrated by this –

SELECT column1
, SUM(foo)
FROM …
GROUP
BY column1
, column2

which is perfectly legal, syntactically, in all databases, if not semantically useful

Huh…I can honestly say I’ve never tried that (I also can’t come up with a scenario where it’ll be useful either, but I’m sure it might be…somehow…). I learned something today.

[shivanaikrachanaikar]

Hi ,
Thanks its working but now the problem is i want to check the particular date is having 2 entries means if you go for today date if it is having 1 entry it needs to allow another entry for that date with same user id and if you are trying to add more then 2 entries(touples) it should throw error.

Please help me and thanks for your reply.

[DaveMaxwell]

HAVING COUNT(*) is what you’re looking for.

To error the ones that have too many…

SELECT field1
     , COUNT(*)
  FROM tableName
 WHERE condition = value
 GROUP BY field1
HAVING COUNT(*) > 2

You can use any combination you want
HAVING COUNT() < 2
HAVING COUNT() <= 2
HAVING COUNT() = 2
HAVING COUNT() >= 2
HAVING COUNT() > 2
HAVING COUNT() <> 2

[shivanaikrachanaikar]

little confusion as i want to insert 2 entries (touples) for each day to each user_id so how i need to restrict that one whether while creating table or inserting to the table.
but this insertion is happening not directly i am using another application to insert data into this table.

[DaveMaxwell]

Use HAVING COUNT(*) < 2. Only allow for a record to be inserted if it is returned in the query.

[shivanaikrachanaikar]

Ok thanks,
I will try

[shivanaikrachanaikar]

Hi
I want one more query that needs to be count the number of entries(tuples) between the provided
dates from the user.

my table is like this
user_id: date : amount
1 01/05/2017 10
1 02/05/2017 20
1 03/05/2017 25
1 04/05/2017 30

ex:user will enter the user_id ,fromdate and toDate so i want how many transactions have been there between those 2 dates and i want the result to be in count (like for above table 4) from the above table to proceed my calculation.

Please help me to write the query .

[DaveMaxwell]

It’s the same query - you just need to add a condition in your where clause BETWEEN is the keyword to use.

WHERE fieldName BETWEEN date1 AND date2

[shivanaikrachanaikar]

Hi,
I have written query but its getting error.
SQL> select count(*) from Dailyinfo where User_id=1011 and
Today_date between to_date(‘02/05/2017’) and to_date(‘06/05/2017’);

Error is:->
Today_date between to_date(‘02/05/2017’) and to_date(‘06/05/2017’)
not a valid month

[DaveMaxwell]

@shivanaikrachanaikar

select count(*) from Dailyinfo where User_id=1011 and Today_date between to_date(‘02/05/2017’) and to_date(‘06/05/2017’);

today_date is not the same name as you put in your table structure above, but you don’t need to convert the dates.

SELECT COUNT(*)
  FROM DailyInfo
 WHERE user_id = 1011
   AND today_date BETWEEN '02/05/2017' AND '06/05/2017'

[r937]

@DaveMaxwell

you don’t need to convert the dates

oh ye of too much faith

TO_DATE is Oracle – OP might just have to convert using a format string

TO_DATE('02/05/2017','DD/MM/YYYY')