Robert Androsz Robert Androsz - 24 days ago 6
PHP Question

Select statement with count and without

I have a SQL statement to select data:

SELECT DISTINCT count(table.data) as cntd, table.datatwo
FROM table LEFT JOIN tabletwo ON table.id=tabletwo.tableid
WHERE tabletwo.dated='2016-11-17' AND table.datafive='1'


If I keep it the way above - it returns the data as below:

cntd datatwo
4 92


However the correct data should be:

cntd datatwo
2 92
2 93


As there are two different
datatwo
. When I select the statement without cont - it shows both
datatwo
.

datatwo
92
93


Where am I making a mistake?

Answer

Despite how random and unintuitive your table naming is, it appears what you want is to use a GROUP BY statement:

SELECT
    DISTINCT count(table.data) as cntd,
    table.datatwo
FROM table
LEFT JOIN tabletwo ON table.id=tabletwo.tableid
WHERE tabletwo.dated='2016-11-17'
  AND table.datafive='1'
GROUP BY tabletwo.tableid

The COUNT statement is an aggregate function so it causes the rows to collapse down to one. Grouping by the unique value for each row will cause it to aggregate in groups.