Query output differs from the expected output

Question

Below query is doing what I need:

SELECT assign.from_uid, assign.aid, assign.message, curriculum.asset, 
       curriculum.title, curriculum.description 
FROM assignment assign 
INNER JOIN curriculum_topics_assets curriculum 
   ON assign.nid = curriculum.asset 
WHERE assign.to_uid = 13 AND assign.status = 1 
GROUP BY assign.from_uid, assign.to_uid, assign.nid 
ORDER BY assign.created DESC

Now I need to get the total count of rows of the result. For example if it is displaying 5 rows the o/p should be like My expected o/p. The query I tried is given below.

SELECT count(description) FROM assignment assign 
INNER JOIN curriculum_topics_assets curriculum ON assign.nid = curriculum.asset 
WHERE assign.to_uid = 13 AND assign.status = 1 
GROUP BY assign.from_uid, assign.to_uid, assign.nid 
ORDER BY assign.created DESC

My expected o/p:

count(*)
---------
5

My current o/p:

count(*)
---------
6
2
5
6
6

@@Harry Joy: please see the edited question

Fero
– Fero

2011-09-07 09:35:34 +00:00
Commented Sep 7, 2011 at 9:35 — Fero
– Fero, Commented Sep 7, 2011 at 9:35

Lieven Keersmaekers · Accepted Answer · 2011-09-07 10:05:39Z

2

The easiest solution would be to

place your initial GROUP BY query in a subselect
select the amount of rows retrieved from this subselect

SQL Statement

SELECT COUNT(*)
FROM   (
          SELECT  assign.from_uid 
          FROM    assignment assign 
                  INNER JOIN curriculum_topics_assets curriculum ON assign.nid = curriculum.asset 
          WHERE   assign.to_uid = 13 
                  AND assign.status = 1 
          GROUP BY 
                  assign.from_uid
                  , assign.to_uid
                  , assign.nid 
       ) q

Edit - why doesn't the original query return the results required

It did already prepared what was needed to get the correct result

Your query without grouping returns a resultset of 25 records (6+2+5+6+6)
From these 25 records, you have 5 unique combinations of from_uid, to_uid, nid

Now you don't want to count how many records each combination has (as you did in your example) but how many unique (distinct anyone?) combinations there are.

One solution to this is the subselect I presented but following equivalent statement using a DISTINCT clause might be more comprehensive.

SELECT  COUNT(*)
FROM    (
          SELECT  DISTINCT assign.from_uid
                  , assign.to_uid
                  , assign.nid
          FROM    assignment assign 
                  INNER JOIN curriculum_topics_assets curriculum ON assign.nid = curriculum.asset 
          WHERE   assign.to_uid = 13 
                  AND assign.status = 1 
        ) q

Note that my personal preference goes to the GROUP BY solution.

edited Sep 7, 2011 at 10:05

answered Sep 7, 2011 at 9:35

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

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

6 Comments

Fero Over a year ago

@@leiven: it's working great.. :-).. One more favour.. Why count(*) doesn't work ?

Lieven Keersmaekers Over a year ago

@Fero - The COUNT(*) in my example is no problem and I don't see one in your question? I don't get what you mean.

ypercubeᵀᴹ Over a year ago

@Lieven: He means why his second query does not work (does not give same result as yours).

Lieven Keersmaekers Over a year ago

@ypercube - <slap forehead> I see. I'll edit the answer to try to clear that up.

Lieven Keersmaekers Over a year ago

@Fero - I have added some additional explanation trying to clear things up.

|

Johan · Accepted Answer · 2011-09-07 09:37:35Z

0

To get the number of rows for a query do:

SELECT COUNT(*) as RowCount FROM (--insert other query here--) s

In you example:

SELECT COUNT(*) as RowCount FROM (SELECT a.from_uid
  FROM assignment a 
  INNER JOIN curriculum_topics_assets c ON a.nid = c.asset 
  WHERE a.to_uid = 13 
    AND a.status = 1 
  GROUP BY a.from_uid, a.to_uid, a.nid 
  ) s

Note that I the dropped the stuff that has no effect on the number of rows to make the query run slightly faster.

answered Sep 7, 2011 at 9:37

Johan

77.4k28 gold badges205 silver badges346 bronze badges

Comments

PerfectGamesOnline.com · Accepted Answer · 2011-09-07 09:34:10Z

-1

You should use COUNT(*) instead of count(description). Look at: http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/

answered Sep 7, 2011 at 9:34

PerfectGamesOnline.com

1,7781 gold badge18 silver badges32 bronze badges

1 Comment

Fero Over a year ago

I did that... But the o/p is same for both COUNT(*) instead of count(description)

Collectives™ on Stack Overflow

Query output differs from the expected output

3 Answers 3

SQL Statement

Edit - why doesn't the original query return the results required

6 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

SQL Statement

Edit - why doesn't the original query return the results required

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related