ERROR 42803 (grouping_error) column must appear in the GROUP BY clause or be used in an aggregate function

Phoenix Forum Questions / Help
, ,
1

I am using this query

    query = from b in Logistics.Box.UHF,
                where: b.admin_id == ^admin_id and b.source == ^location_id,
                left_join: p in Logistics.Products.UHF, on: b.id == p.box_id,
                left_join: c in Logistics.Products.Product, on: p.barcode_id == c.barcode_id,
                group_by: [p.barcode_id],
                select: %{name: c.name, image: c.image, status: b.status, count: count(p.barcode_id)}

Which is throwing this error

ERROR 42803 (grouping_error) column “p2.name” must appear in the GROUP BY clause or be used in an aggregate function

After googling for the past few hours I have been trying various solutions. But none is working.

Can anyone give me insight on how to solve this? Your help is greatly appreciated.

1 Like
2

That query makes not much sense, as you are grouping and aggregating on the same column. Could you post example data and example result you want to get.

3

Sure, when i run the below query.

    query = from b in Logistics.Box.UHF,
                where: b.admin_id == ^admin_id and b.source == ^location_id,
                left_join: p in Logistics.Products.UHF, on: b.id == p.box_id,
                left_join: c in Logistics.Products.Product, on: p.barcode_id == c.barcode_id,
                select: %{name: c.name, image: c.image, status: b.status, barcode_id: p.barcode_id}

I get the below results


[
  %{
    barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
    image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/8f49984066e04c30b091b326b33e8e72.png",
    name: "NEW TV 1",
    status: 3
  },
  %{
    barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
    image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/8f49984066e04c30b091b326b33e8e72.png",
    name: "NEW TV 1",
    status: 3
  },
  %{
    barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
    image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/8f49984066e04c30b091b326b33e8e72.png",
    name: "NEW TV 1",
    status: 3
  },
  %{
    barcode_id: "a439d385-410d-4c8a-bc5b-e37caaadeaa0",
    image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/71fb1e88daeb4dee8c3f39f6fa52c32a.jpg",
    name: "NEW TV 2",
    status: 3
  },
  %{
    barcode_id: "a439d385-410d-4c8a-bc5b-e37caaadeaa0",
    image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/71fb1e88daeb4dee8c3f39f6fa52c32a.jpg",
    name: "NEW TV 2",
    status: 3
  },
  %{
    barcode_id: "a439d385-410d-4c8a-bc5b-e37caaadeaa0",
    image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/71fb1e88daeb4dee8c3f39f6fa52c32a.jpg",
    name: "NEW TV 2",
    status: 1
  },
  %{
    barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
    image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/8f49984066e04c30b091b326b33e8e72.png",
    name: "NEW TV 1",
    status: 1
  },
  %{
    barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
    image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/8f49984066e04c30b091b326b33e8e72.png",
    name: "NEW TV 1",
    status: 1
  },
  %{
    barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
    image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/8f49984066e04c30b091b326b33e8e72.png",
    name: "NEW TV 1",
    status: 1
  },
  %{
    barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
    image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/8f49984066e04c30b091b326b33e8e72.png",
    name: "NEW TV 1",
    status: 1
  }
]

In the above results, you can see that barcode_id is the same for several results.

If the barcode id is same then the name and image also belong to that barcode id only which will be same too.

Now i want to know the count of barcode_id plus i want to group by barcode id.

I am expecting this kind of result


[
  %{
    barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
    image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/8f49984066e04c30b091b326b33e8e72.png",
    name: "NEW TV 1",
    count: 7   (Count of barcode ID)
  },
  %{
    barcode_id: "a439d385-410d-4c8a-bc5b-e37caaadeaa0",
    image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/71fb1e88daeb4dee8c3f39f6fa52c32a.jpg",
    name: "NEW TV 2",
    count: 3	(Count of barcode ID)
  }
]
4

In SQL you cannot select fields that you aren’t grouping by, for simple reason, if there is different values in given field, then which one should be presented?

And if you wand just the count then you can do it in a subquery:

from b in Logistics.Box.UHF,
  where: b.admin_id == ^admin_id and b.source == ^location_id,
  lateral_join: p in fragment("SELECT p.barcode_id, COUNT(*) FROM logistics_products_uhf p WHERE p.box_id = ?",  b.id),
  join: c in Logistics.Products.Product, on: p.barcode_id == c.barcode_id,
  select: %{name: c.name, image: c.image, status: b.status, count: p.count}

You additionally probably do not want left_joins and you probably want inner_joins (which are used by default when used as join).

2 Likes
5

Thank you very much for your response @hauleth

I tried your query
First time I got this error

unsupported :lateral_join in keyword query expression

then I changed lateral_join to inner_lateral_join and ran the query.

    query = from b in Logistics.Box.UHF,
              where: b.admin_id == ^admin_id and b.source == ^location_id,
              inner_lateral_join: p in fragment("SELECT p.barcode_id, COUNT(*) FROM product_uhf p WHERE p.box_id = ?",  b.id),
              join: c in Logistics.Products.Product, on: p.barcode_id == c.barcode_id,
              select: %{name: c.name, image: c.image, status: b.status, count: p.count}
    Repo.all(query)

Now i am getting the group_by error

ERROR 42803 (grouping_error) column “p.barcode_id” must appear in the GROUP BY clause or be used in an aggregate function

Any idea where I am going wrong?

6

It should be:

SELECT p.barcode_id, COUNT(*) FROM product_uhf p WHERE p.box_id = ? GROUP BY 1

Sorry for that.

1 Like
7

Thanks your query was really helpful. I learned new things from it.

But still i could not get the exact result which i want.

Your query above is group_by barcode_id but count is done based on box_id.

For example 

For Box 1 -> barcode_1 contains 3  and barcode_2 contains 2 count
      Box  2 -> barcode_1 contains 4 and barcode_2 contains 1 count

But i want barcode_1 contains 7 and barcode_2 contains 3 count.

I want the overall count not group_by box_id

8

I got the output using the subquery.

    query   = from b in Logistics.Box.UHF,
                where: b.admin_id == ^admin_id and b.source == ^location_id,
                join: p in Logistics.Products.UHF, on: b.id == p.box_id,
                group_by: [p.barcode_id],
                select: %{barcode_id: p.barcode_id, count: count(p.barcode_id)}

    query_2 = from q in subquery(query),
                join: c in Logistics.Products.Product, on: q.barcode_id == c.barcode_id,
                select: %{barcode_id: q.barcode_id, count: q.count, name: c.name, image: c.image}

   Repo.all(query_2)

Output:

[
  %{
    barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
    count: 7,
    image: "https://s3.ap-south-1.amazonaws.com/8f49984066e04c30b091b326b33e8e72.png",
    name: "NEW TV 1"
  },
  %{
    barcode_id: "a439d385-410d-4c8a-bc5b-e37caaadeaa0",
    count: 3,
    image: "https://s3.ap-south-1.amazonaws.com/71fb1e88daeb4dee8c3f39f6fa52c32a.jpg",
    name: "NEW TV 2"
  }
]

Thank you very much hauleth. I learnt some new things from you:-)

2 Likes
9

In future - my approach to such queries is:

  1. Write it in pure SQL
  2. Run it with dadbod
  3. Check query plan (EXPLAIN ANALYZE)
  4. Optimise it
  5. Repeat 2 to 4 as long as needed
  6. Translate SQL query to Ecto.Query syntax

Sometimes it is much simpler to work with raw SQL, especially when building queries and testing them out. At least it is simpler for me, as I do not need to think in 2 languages.

5 Likes