How to get count with *

Question

Select Count(*),* from TourBooking Where MemberID = 6

Giving an error

"Column 'TourBooking.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

@HelpingHands I also want all data from table not only count — Mike, Feb 10 '15 at 09:32
Do you want the count for `memberid = 6` or the total count of all rows in that table (regardless of your `where` clause) — , Feb 10 '15 at 09:36
Can't you just `select * .. where MemberID = 6` and then count the number of results? — Hans Keﬆing, Feb 10 '15 at 09:38
possible duplicate of [GROUP BY / aggregate function confusion in SQL](http://stackoverflow.com/questions/4611897/group-by-aggregate-function-confusion-in-sql) — Tanner, Feb 10 '15 at 09:39

score 2 · Answer 1 · answered Feb 10 '15 at 09:36

2

You need to use a subselect:

Select (select Count(*) from TourBooking Where MemberID = 6), * 
    from TourBooking 
Where MemberID = 6

answered Feb 10 '15 at 09:36

Christian Barron

2,695
1
14
22

score 2 · Accepted Answer · answered Feb 10 '15 at 09:38

count() is an aggregate function and thus requires a group by. If you want to count the total number of rows in your result, you can use a window function to add such a column:

select count(*) over () as total_count, 
       TourBooking.*
from TourBooking
where MemberID = 6;

If you want the total count in the table TourBooking regardless of the value in the column MemberId you need a scalar sub-query in order to retrieve the count:

Select (select Count(*) from TourBooking) as total_count,
       TourBooking.*
from TourBooking 
where MemberID = 6

score -2 · Answer 3 · edited Feb 10 '15 at 09:38

-2

Please change your code from

Select Count(*),* from TourBooking Where MemberID = 6

into

Select Count(*) from TourBooking Where MemberID = 6

edited Feb 10 '15 at 09:38

answered Feb 10 '15 at 09:37

whywhy

97
7

don't use code snippets for sql, just use 4 spaces at the start or use the code sample button `{}` – Tanner Feb 10 '15 at 09:38

How to get count with *

3 Answers3