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

Question

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();
        });
    }

add the table's schema in the question.

DsRaj
– DsRaj

2018-07-09 10:07:49 +00:00
Commented Jul 9, 2018 at 10:07 — DsRaj
– DsRaj, Commented Jul 9, 2018 at 10:07
@DsRaj, i have added the schema

Switz
– Switz

2018-07-09 10:12:02 +00:00
Commented Jul 9, 2018 at 10:12 — Switz
– Switz, Commented Jul 9, 2018 at 10:12

Danoctum · Accepted Answer · 2018-07-09 09:54:57Z

1

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!

answered Jul 9, 2018 at 9:54

Danoctum

3516 silver badges17 bronze badges

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

3 Comments

Loek Over a year ago

Advocate of the devil: I highly encourage doing queries like this. It makes your code framework independent and probably faster since you don't need to load an entire ORM for a relatively easy select.

Danoctum Over a year ago

If this is the only table in the database I would have to agree, but using this schema for bigger projects makes it inconvenient in the long term. I do not know the size of Switz' project though.

Loek Over a year ago

That's taste I think. Very valid point but it doesn't weigh up to my need of being independent from frameworks :)

Ved · Accepted Answer · 2018-07-09 10:02:40Z

0

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

answered Jul 9, 2018 at 10:02

Ved

7464 silver badges8 bronze badges

1 Comment

Switz Over a year ago

How would this query stand if the response are not Yes or No response? I added the Yes or No as an example of responses that could be the same

DsRaj · Accepted Answer · 2018-07-09 10:31:08Z

0

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

answered Jul 9, 2018 at 10:31

DsRaj

2,3181 gold badge19 silver badges27 bronze badges

1 Comment

DsRaj Over a year ago

You can add the count of the 'no' answer by own so you will get better idea

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related