CODEWITHSUNDEEP
phplaravel
Switz
Switz

Reputation: 473

Displaying duplicates and non-duplicates data from column in database - laravel

In my database, i have a list of responses collected from customers that i want to gather and generate a pie chart for. (like that of google forms).

In my database, i have responses that are the same and other not like below

Client 1   Client 2    Client
  Yes        Yes         No

So in the column above, i have Yes(2) and No(1). I want to gather, count and display my query response like below

Yes : 2  , No: 1

But with my code below

 public function answers_chart(Survey $survey)
   {
         $response = DB::select( DB::raw(" select * from (
          SELECT questionnaire_id, COUNT(answer) as e_count
          FROM Answer
          GROUP BY answer
          )a where  a.e_count > 1)); 
   }

I get the response like below, meaning it is only counting the Yes

[{"e_count":2}]

How can i achieve something like this?

Table

public function up()

 {
        Schema::create('Answer', function (Blueprint $table) {
            $table->increments('id');         
            $table->integer('question_id');
            $table->integer('questionnaire_id');
            $table->string('answer');
            $table->timestamps();
        });
    }

Upvotes: 0

Views: 558

Answers (3)

DsRaj
DsRaj

Reputation: 2328

Try this:

Model::select(DB::raw("COUNT(CASE WHEN answer = 'Yes' THEN 1 END) AS e_count"))->first(); // Eloquent approach

DB::table('answer')->select(DB::raw("COUNT(CASE WHEN answer = 'Yes' THEN 1 END) AS e_count"))->first(); // Query Builder approach

Upvotes: 0

Ved
Ved

Reputation: 746

may be using case statments will be better, for example 

SELECT
COUNT(CASE WHEN answer = '1' THEN 1 END) AS yes,
COUNT(CASE WHEN answer= '0' THEN 1 END) AS no
FROM ANSWER

Upvotes: 0

Danoctum
Danoctum

Reputation: 341

You have a.e_count > 1. This means that the GROUP BY answer has only 1 'No' in it and you only want the ones that has more than 1 of 'No' or 'Yes'. You should remove a where a.e_count > 1).

I highly discourage doing queries like this though. Laravel has a powerful built in Eloquent ORM. Take a look at it here for better use of the Laravel framework!

Upvotes: 1

Related Questions